On 05/13/2014 05:39 AM, Michelle Sullivan via RT wrote: > [snip] This message was rejected from rt-users because it was over 1M of logfiles. Please don't send large messages to the list; you're welcome to provide a link to complete logs for those interested in them, of course.
> Here's the result of what RT4 does to the DB (note: RT3.8 does *NOT* > suffer the same problems - as am running that and just trying to > upgrade.. Also note: RT 4.0.19 on Apache 2.2 with ModPerl2 running > against some very fast PostgreSQL 8.4.10 DBs (cluster of 4 servers) - > round trip to them is around 25-50ms)... the query log for a *SINGLE* > ticket follows... This only occurs on the first request; DBIx::SearchBuilder calls DBI->column_info to determine the set of columns for each RT table, to be able to use them in the GROUP BY. It then caches the information; none of the queries to pg_constraints should happen after the first request. There are two fixes to this; one is to find a way to not call DBIx::SearchBuilder->Fields, which is what calls DBI->column_info, which causes the slow queries. RT is currently using the set of columns so obtained to pass to GROUP BY to perform both a DISTINCT and ORDER BY; Pg 9.1 is smart enough to know that grouping by the primary key is enough to imply the remaining columns as well[1]. As such, the attached patch (which has only been briefly tested) may suffice. This is only slightly papering around the problem, however, as any later call to ->Fields will incur the same cost. RT does not currently contain any such calls, so I _expect_ it to be sufficient. A more robust solution would be to call ->Fields at server initialization time, before the first request. This would require also modifying DBIx::SearchBuilder::Handle to cache the information by DSN, as it currently only lasts until the dbh gets replaced -- which happens at server startup. > This has to be a bug of RT4 or of the DBIx modules it uses so cross > posting to rt-bugs@ as well... Please don't cross-post to rt-bugs@; it leads to duplicate tickets. Once discussion has achieved consensus on the bug is the right time to create a bug ticket. - Alex [1] http://www.postgresql.org/docs/9.1/static/release-9-1.html#AEN109008
>From 8cf21b5cadd6205f35f29028dedd9b425e4a6685 Mon Sep 17 00:00:00 2001 From: Alex Vandiver <[email protected]> Date: Tue, 13 May 2014 15:04:30 -0400 Subject: [PATCH] We can omit the (heavy on Pg) ->Fields call on 9.1 and above --- lib/DBIx/SearchBuilder/Handle/Pg.pm | 12 +++++++++--- 1 file changed, 9 insertions(+), 3 deletions(-) diff --git a/lib/DBIx/SearchBuilder/Handle/Pg.pm b/lib/DBIx/SearchBuilder/Handle/Pg.pm index 14be2bf..94ce76a 100755 --- a/lib/DBIx/SearchBuilder/Handle/Pg.pm +++ b/lib/DBIx/SearchBuilder/Handle/Pg.pm @@ -235,9 +235,15 @@ sub DistinctQuery { # It's hard to show with tests. Pg's optimizer can choose execution # plan not guaranting order - # So if we are ordering by something that is not in 'main', the we GROUP - # BY all columns and adjust the ORDER_BY accordingly - local $sb->{group_by} = [ map {+{FIELD => $_}} $self->Fields($table) ]; + my $groups; + if ($self->DatabaseVersion =~ /^(\d+)\.(\d+)/ and ($1 > 9 or ($1 == 9 and $2 >= 1)) { + # Pg 9.1 supports "SELECT main.foo ... GROUP BY main.id" if id is the primary key + $groups = [ {FIELD => "id"} ]; + } else { + # For earlier versions, we have to list out all of the columns + $groups = [ map {+{FIELD => $_}} $self->Fields($table) ]; + } + local $sb->{group_by} = $groups; local $sb->{'order_by'} = [ map { ($_->{'ALIAS'}||'') ne "main" -- 1.9.3
-- RT Training - Dallas May 20-21 http://bestpractical.com/training
