Received: from maia.hub.org (maia-3.hub.org [200.46.204.243]) by mail.postgresql.org (Postfix) with ESMTP id B06061337CAA for ; Tue, 12 Apr 2011 21:18:12 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.243]) (amavisd-maia, port 10024) with ESMTP id 50225-03-4 for ; Wed, 13 Apr 2011 00:18:04 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from web39703.mail.mud.yahoo.com (web39703.mail.mud.yahoo.com [209.191.106.49]) by mail.postgresql.org (Postfix) with SMTP id 99DC91337CF1 for ; Tue, 12 Apr 2011 21:14:30 -0300 (ADT) Received: (qmail 3388 invoked by uid 60001); 13 Apr 2011 00:14:29 -0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=yahoo.com; s=s1024; t=1302653669; bh=r2lXkDN4QQZdfyRjuHz/uGKYlcZTYWp0aBuQxxsEbj4=; h=Message-ID:X-YMail-OSG:Received:X-Mailer:Date:From:Subject:To:Cc:In-Reply-To:MIME-Version:Content-Type:Content-Transfer-Encoding; b=4RjZP45UWZlmyODg6euXX1JMusKtJdR0/6Sk9GudNqo7svD6IHsuEpe1Deyz/SaUQXMx4617FJJ/UB/XlDLALxn0SYiJMfyQTHdO0XHCKpyCKbLOjHEIF3WKH/Ix3zZiGuS6z9JjzYCsE5/+G+57n/5+mE00BUm30H0HOX4w7HU= DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.com; h=Message-ID:X-YMail-OSG:Received:X-Mailer:Date:From:Subject:To:Cc:In-Reply-To:MIME-Version:Content-Type:Content-Transfer-Encoding; b=OhJ/ytaasNmVx0EnAhfQYqcuHHTziCTEmqhpOB7YDLoM5Ftnk9WRZguhyFEC4aX2IsvXp5ZDYlzaRfdVyKSgOdQW0rEMsrS60qschbK317ljTSe3Aw6WFKzhJwtnu7fu3UBnQ9h1s6H4Eo3qFbf7Vr8RKTTR/yzX0eBfNtaHSF0=; Message-ID: <867169.80414.qm@web39703.mail.mud.yahoo.com> X-YMail-OSG: K8vwDb4VM1kI0lhnsF2tj2MqwPS6RiWPyl0HZfowJX48wA1 fMfsPj8KNyFASGAP3O3NnATp14Eu2ONUxcuduiWp4yoXKRZuWL.14bYd_1lM cUtW3l8oeqO2EZlKvpGBrRn9rDKpt0yX2_uyHqQ5XcHe1vcuH5MP_gJDNXPN io22orFmZn.ZVR8t2q2qmNwX_4yOYNWh.PDKL9AiuTvzcvSTvjNQObaL9pA4 9LgP65KnUC2ZSpDwRh6LDzuAzEsfCZF28NfAXyhlgFsG7MDylUeWb5AEsxHh oHEx5njScd8nlAOLbyGsK_9p9x_LvzbUXUznzgdw7TA7TW8jas47L3fEjK33 5yS3KDz26zGk0DhpfHn9Xvudmv_1kqGumArKhn027JAr_XSY3cV52C_m2Hgc KhdtFUf8K6e7IYQ-- Received: from [69.76.130.83] by web39703.mail.mud.yahoo.com via HTTP; Tue, 12 Apr 2011 17:14:29 PDT X-Mailer: YahooMailClassic/12.0.2 YahooMailWebService/0.8.109.295617 Date: Tue, 12 Apr 2011 17:14:29 -0700 (PDT) From: Bob Lunney Subject: Re: poor execution plan because column dependence To: =?iso-8859-1?Q?V=E1clav_Ovs=EDk?= Cc: pgsql-performance@postgresql.org In-Reply-To: <20110412232343.GA15966@bobek.localdomain> MIME-Version: 1.0 Content-Type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: quoted-printable X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.908 tagged_above=-10 required=5 tests=BAYES_00=-1.9, FREEMAIL_FROM=0.001, RCVD_IN_DNSWL_NONE=-0.0001, RFC_ABUSE_POST=0.001, T_RP_MATCHES_RCVD=-0.01 X-Spam-Level: X-Archive-Number: 201104/179 X-Sequence-Number: 43241 Zito,=0A=0AUsing psql log in as the database owner and run "analyze verbose= ". Happiness will ensue.=0A=0AAlso, when requesting help with a query its = important to state the database version ("select version();") and what, if = any, configuration changes you have made in postgresql.conf. Listing ony = the ones that have changed is sufficient.=0A=0AFinally, the wiki has some g= ood information on the care and feeding of a PostgreSQL database:=0A=0Ahttp= ://wiki.postgresql.org/wiki/Introduction_to_VACUUM,_ANALYZE,_EXPLAIN,_and_C= OUNT=0A=0A=0A=0ABob Lunney=0A=0A--- On Tue, 4/12/11, V=E1clav Ovs=EDk wrote:=0A=0A> From: V=E1clav Ovs=EDk =0A>= Subject: [PERFORM] poor execution plan because column dependence=0A> To: p= gsql-performance@postgresql.org=0A> Date: Tuesday, April 12, 2011, 7:23 PM= =0A> Hi,=0A> I have done migration of the Request Tracker 3.8.9=0A> (http:/= /requesttracker.wikia.com/wiki/HomePage) from=0A> Mysql to=0A> PostgreSQL i= n testing environment.=0A> The RT schema used can be viewed at=0A> https://= github.com/bestpractical/rt/blob/3.8-trunk/etc/schema.Pg.=0A> I have added = full text search on table Attachments based on=0A> trigrams=0A> (and still = experimenting with it), but is is not=0A> interesting for the=0A> problem (= the problem is not caused by it directly).=0A> The full text search alone w= orks quite good. A user testing=0A> a new RT instance=0A> reported a poor p= erformance problem with a bit more complex=0A> query (more=0A> conditions r= esulting in table joins).=0A> Queries are constructed by module DBIx::Searc= hBuilder.=0A> The problematic query logged:=0A> =0A> rt=3D# EXPLAIN ANALYZE= SELECT DISTINCT=A0 main.* FROM=0A> Tickets main JOIN Transactions Transact= ions_1=A0 ON (=0A> Transactions_1.ObjectId =3D main.id ) JOIN Attachments= =0A> Attachments_2=A0 ON ( Attachments_2.TransactionId =3D=0A> Transactions= _1.id )=A0 WHERE (Transactions_1.ObjectType =3D=0A> 'RT::Ticket') AND (main= .Status !=3D 'deleted') AND=0A> (main.Status =3D 'resolved' AND main.LastUp= dated >=0A> '2008-12-31 23:00:00' AND main.Created > '2005-12-31=0A> 23:00:= 00' AND main.Queue =3D '15' AND=A0 (=0A> Attachments_2.trigrams @@ text_to_= trgm_tsquery('uir') AND=0A> Attachments_2.Content ILIKE '%uir%' ) ) AND (ma= in.Type =3D=0A> 'ticket') AND (main.EffectiveId =3D main.id)=A0 ORDER BY=0A= > main.id ASC;=0A> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0=0A> =A0 =A0 =A0 =A0 =A0 = =A0 =A0 =A0=0A> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0=0A> =A0 =A0 =A0 =A0 =A0 =A0= =A0 =A0=0A> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0=0A> =A0 =A0 =A0 =A0 =A0 =A0 = =A0 =A0=0A> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0=0A> =A0 =A0 =A0 =A0 =A0 =A0 =A0= =A0=0A> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0=0A> =A0 =A0 =A0 =A0 =A0 =A0 =A0 = =A0=0A> =A0 =A0 =A0 =A0 =A0=A0=A0QUERY=0A> PLAN=A0 =A0 =A0 =A0 =A0 =A0 =A0 = =A0=0A> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0=0A> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0= =0A> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0=0A> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0=0A= > =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0=0A> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0=0A> = =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0=0A> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0=0A> =A0= =A0 =A0 =A0 =A0 =A0 =A0 =A0=0A> =A0 =A0 =A0 =A0 =A0 =A0 =0A> -------------= ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= ----------------------------------------=0A> Unique=A0 (cost=3D23928.60..2= 3928.67 rows=3D1 width=3D162)=0A> (actual time=3D5201.139..5207.965 rows=3D= 649 loops=3D1)=0A> =A0=A0=A0->=A0 Sort=A0=0A> (cost=3D23928.60..23928.61 ro= ws=3D1 width=3D162) (actual=0A> time=3D5201.137..5201.983 rows=3D5280 loops= =3D1)=0A> =A0 =A0 =A0 =A0=A0=A0Sort Key:=0A> main.effectiveid, main.issuest= atement, main.resolution,=0A> main.owner, main.subject, main.initialpriorit= y,=0A> main.finalpriority, main.priority, main.timeestimated,=0A> main.time= worked, main.timeleft, main.told, main.starts,=0A> main.started, main.due, = main.resolved, main.lastupdatedby,=0A> main.lastupdated, main.creator, main= .created, main.disabled=0A> =A0 =A0 =A0 =A0=A0=A0Sort Method:=A0=0A> quicks= ort=A0 Memory: 1598kB=0A> =A0 =A0 =A0 =A0=A0=A0->=A0 Nested=0A> Loop=A0 (co= st=3D0.00..23928.59 rows=3D1 width=3D162) (actual=0A> time=3D10.060..5120.8= 34 rows=3D5280 loops=3D1)=0A> =A0 =A0 =A0 =A0 =A0 =A0=0A> =A0=A0=A0->=A0 Ne= sted Loop=A0=0A> (cost=3D0.00..10222.38 rows=3D1734 width=3D166) (actual=0A= > time=3D8.702..1328.970 rows=3D417711 loops=3D1)=0A> =A0 =A0 =A0 =A0 =A0 = =A0 =A0 =A0=0A> =A0 =A0=A0=A0->=A0 Seq Scan on tickets=0A> main=A0 (cost=3D= 0.00..5687.88 rows=3D85 width=3D162) (actual=0A> time=3D8.258..94.012 rows= =3D25410 loops=3D1)=0A> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0=0A> =A0 =A0 =A0 =A0= =A0=A0=A0Filter:=0A> (((status)::text <> 'deleted'::text) AND (lastupdated= =0A> > '2008-12-31 23:00:00'::timestamp without time zone) AND=0A> (created= > '2005-12-31 23:00:00'::timestamp without time=0A> zone) AND (effectiveid= =3D id) AND (queue =3D 15) AND=0A> ((type)::text =3D 'ticket'::text) AND (= (status)::text =3D=0A> 'resolved'::text))=0A> =A0 =A0 =A0 =A0 =A0 =A0 =A0 = =A0=0A> =A0 =A0=A0=A0->=A0 Index Scan using=0A> transactions1 on transactio= ns transactions_1=A0=0A> (cost=3D0.00..53.01 rows=3D27 width=3D8) (actual t= ime=3D0.030..0.039=0A> rows=3D16 loops=3D25410)=0A> =A0 =A0 =A0 =A0 =A0 =A0= =A0 =A0=0A> =A0 =A0 =A0 =A0 =A0=A0=A0Index Cond:=0A> (((transactions_1.obj= ecttype)::text =3D 'RT::Ticket'::text)=0A> AND (transactions_1.objectid =3D= main.effectiveid))=0A> =A0 =A0 =A0 =A0 =A0 =A0=0A> =A0=A0=A0->=A0 Index Sc= an using attachments2=0A> on attachments attachments_2=A0 (cost=3D0.00..7.8= 9 rows=3D1=0A> width=3D4) (actual time=3D0.008..0.009 rows=3D0 loops=3D4177= 11)=0A> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0=0A> =A0 =A0=A0=A0Index Cond:=0A> (a= ttachments_2.transactionid =3D transactions_1.id)=0A> =A0 =A0 =A0 =A0 =A0 = =A0 =A0 =A0=0A> =A0 =A0=A0=A0Filter: ((attachments_2.trigrams @@=0A> '''uir= '''::tsquery) AND (attachments_2.content ~~*=0A> '%uir%'::text))=0A> Total= runtime: 5208.149 ms=0A> (14 rows)=0A> =0A> The above times are for alread= y cached data (repeated=0A> query).=0A> I think the execution plan is poor.= Better would be to=0A> filter table attachments=0A> at first and then join= the rest. The reason is a bad=0A> estimate on number of rows=0A> returned = from table tickets (85 estimated -> 25410 in=0A> the reality).=0A> Eliminat= ing sub-condition...=0A> =0A> =0A> rt=3D# explain analyze select * from tic= kets where=0A> effectiveid =3D id;=0A> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0=0A> = =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0=0A> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0=0A> =A0= QUERY PLAN=A0 =A0 =A0 =A0 =A0 =A0=0A> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0=0A> = =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0=0A> =A0 =A0 =A0 =0A> ----------------------= ---------------------------------------------------------------------------= -------------=0A> Seq Scan on tickets=A0 (cost=3D0.00..4097.40 rows=3D530= =0A> width=3D162) (actual time=3D0.019..38.130 rows=3D101869 loops=3D1)=0A>= =A0=A0=A0Filter: (effectiveid =3D id)=0A> Total runtime: 54.318 ms=0A> (3= rows)=0A> =0A> Estimated 530 rows, but reality is 101869 rows.=0A> =0A> Th= e problem is the strong dependance between id and=0A> effectiveid. The RT= =0A> documentation says:=0A> =0A> =A0 =A0 EffectiveId:=0A> =A0 =A0 By defau= lt, a ticket's EffectiveId is the=0A> same as its ID. RT supports the=0A> = =A0 =A0 ability to merge tickets together. When you=0A> merge a ticket into= =0A> =A0 =A0 another one, RT sets the first ticket's=0A> EffectiveId to the= second=0A> =A0 =A0 ticket's ID. RT uses this data to quickly=0A> look up w= hich ticket=0A> =A0 =A0 you're really talking about when you=0A> reference = a merged ticket.=0A> =0A> =0A> I googled the page http://wiki.postgresql.or= g/wiki/Cross_Columns_Stats=0A> =0A> Maybe I identified the already document= ed problem. What I=0A> can do with this=0A> situation? Some workaround?=0A>= =0A> Thanks in advance for any suggestions.=0A> Best Regards=0A> -- =0A> Z= ito=0A> =0A> -- =0A> Sent via pgsql-performance mailing list (pgsql-perform= ance@postgresql.org)=0A> To make changes to your subscription:=0A> http://w= ww.postgresql.org/mailpref/pgsql-performance=0A> =0A=0A=0A