[rt-users] DBIx::SearchBuilder::Handle::Pg -- again

[Ruslan Zakirov]

We will be happy to improve Pg performance, however my simple tests on
almost Pg DB shows that new queries are slower. I'm comparing
execution plans at this point. Can you grab queries that benefit from
such change and send me execution plans with this patch and without.

On Tue, Dec 23, 2008 at 6:43 PM, Kenneth Marshall <ktm at rice.edu> wrote:
> FYI,
>
> This change has not been made to DBIx::SearchBuilder::Handle::Pg
> for the definition of DistinctQuery. I just checked and you can
> simply use the same definition of DistinctQuery for PostgreSQL
> that you are using for Oracle in DBIx::SearchBuilder::Handle::Oracle.
> This make a substantial performance improvement for RT with a
> PostgreSQL backend database. It would be great if this change
> could be rolled into the next update to DBIx::SearchBuilder.
>
> Happy Holidays,
> Ken
>
> On Thu, Feb 07, 2008 at 08:21:39AM -0600, Kenneth Marshall wrote:
>> Dear DBIx::SearchBuilder developers:
>>
>> Here is a re-send of a message that I posted in January of 2007
>> regarding a change in the DistinctQuery handling for PostgreSQL.
>> Using the version from the Oracle definition is a big performance
>> win. Would it be possible to include this change in the next
>> update to DBIx::SearchBuilder?
>>
>> Cheers,
>> Ken
>>
>> ----- Forwarded message from Kenneth Marshall <ktm at rice.edu> -----
>>
>> Date: Tue, 30 Jan 2007 10:23:52 -0600
>> From: Kenneth Marshall <ktm at rice.edu>
>> To: rt-users at lists.bestpractical.com
>> Subject: [rt-users] DBIx::SearchBuilder::Handle::Pg
>>
>> Just an FYI. In preliminary testing, using the Handle::Oracle
>> definition for the DistinctQuery definition in Handle::Pg provides
>> quite a performance improvement. Here is the original line:
>>
>> $$statementref = "SELECT DISTINCT main.* FROM $$statementref";
>>
>> and the line from Handle::Oracle that should replace it:
>>
>> $$statementref = "SELECT main.* FROM ( SELECT DISTINCT main.id FROM $$statementref ) distinctquery, $table main WHERE (main.id = distinctquery.id) ";
>>
>> Ken Marshall
>> _______________________________________________
>> http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-users
>>
>> Community help: http://wiki.bestpractical.com
>> Commercial support: sales at bestpractical.com
>>
>> Discover RT's hidden secrets with RT Essentials from O'Reilly Media.
>> Buy a copy at http://rtbook.bestpractical.com
>> ----- End forwarded message -----
>> _______________________________________________
>> http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-users
>>
>> Community help: http://wiki.bestpractical.com
>> Commercial support: sales at bestpractical.com
>>
>>
>> Discover RT's hidden secrets with RT Essentials from O'Reilly Media.
>> Buy a copy at http://rtbook.bestpractical.com
>>
> _______________________________________________
> http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-users
>
> Community help: http://wiki.bestpractical.com
> Commercial support: sales at bestpractical.com
>
>
> Discover RT's hidden secrets with RT Essentials from O'Reilly Media.
> Buy a copy at http://rtbook.bestpractical.com
>



-- 
Best regards, Ruslan.