X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id CF49252A55 for ; Wed, 2 Mar 2005 06:51:08 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 48034-02 for ; Wed, 2 Mar 2005 06:51:07 +0000 (GMT) Received: from fep3.cogeco.net (smtp.cogeco.net [216.221.81.25]) by svr1.postgresql.org (Postfix) with ESMTP id CACC652A4F for ; Wed, 2 Mar 2005 06:51:05 +0000 (GMT) Received: from windsortransportationclub.com (d57-59-156.home.cgocable.net [24.57.59.156]) by fep3.cogeco.net (Postfix) with SMTP id BA29C4A4F for ; Wed, 2 Mar 2005 01:51:05 -0500 (EST) Received: (qmail 31107 invoked from network); 2 Mar 2005 07:22:00 -0000 Received: from d36-80-110.home1.cgocable.net (HELO KEN) (24.36.80.110) by 0 with SMTP; 2 Mar 2005 07:22:00 -0000 Message-ID: <001001c51ef4$3849eeb0$6a01a8c0@KEN> From: "Ken Egervari" To: Subject: Help with tuning this query Date: Wed, 2 Mar 2005 01:51:11 -0500 MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_000D_01C51ECA.4F338250" X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 6.00.2900.2180 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2180 X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=1.657 tagged_above=0 required=5 tests=AWL, FORGED_RCVD_HELO, HTML_50_60, HTML_MESSAGE, RCVD_IN_NJABL_DUL, RCVD_IN_SORBS_DUL X-Spam-Level: * X-Archive-Number: 200503/23 X-Sequence-Number: 10728 This is a multi-part message in MIME format. ------=_NextPart_000_000D_01C51ECA.4F338250 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable I've tried to use Dan Tow's tuning method and created all the right = indexes from his diagraming method, but the query still performs quite = slow both inside the application and just inside pgadmin III. Can = anyone be kind enough to help me tune it so that it performs better in = postgres? I don't think it's using the right indexes, or maybe postgres = needs special treatment. I've converted the below query to SQL from a Hibernate query, so the = syntax is probably not perfect but it's semantics are exactly the same. = I've done so by looking at the source code, but I can't run it to get = the exact SQL since I don't have the database on my home machine. select s.* from shipment s inner join carrier_code cc on s.carrier_code_id =3D cc.id inner join carrier c on cc.carrier_id =3D c.id inner join carrier_to_person ctp on ctp.carrier_id =3D c.id inner join person p on p.id =3D ctp.person_id inner join shipment_status cs on s.current_status_id =3D cs.id inner join release_code rc on cs.release_code_id =3D rc.id left join shipment_status ss on ss.shipment_id =3D s.id where p.id =3D :personId and s.is_purged =3D false and rc.number =3D '9' and cs is not null and cs.date >=3D current_date - 31 order by cs.date desc Just assume I have no indexes for the moment because while some of the = indexes I made make it work faster, it's still around 250 milliseconds = and under heavy load, the query performs very badly (6-7 seconds). For your information: shipment contains 40,000 rows shipment_status contains 80,000 rows release_code contains 8 rows person contains 300 rows carrier contains 60 rows carrier_code contains 70 rows The filter ratios are: rc.number =3D '9' (0.125) cs.date >=3D current_date - 31 (.10) p.id =3D ? (0.003) s.is_purged =3D false (.98) I really hope someone can help since I'm pretty much stuck. Best regards and many thanks, Ken ------=_NextPart_000_000D_01C51ECA.4F338250 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable
I've tried to use Dan Tow's tuning = method and=20 created all the right indexes from his diagraming method, but the query = still=20 performs quite slow both inside the application and just inside pgadmin=20 III.  Can anyone be kind enough to help me tune it so that it = performs=20 better in postgres?  I don't think it's using the right indexes, or = maybe=20 postgres needs special treatment.
 
I've converted the below query to SQL = from a=20 Hibernate query, so the syntax is probably not perfect but it's = semantics=20 are exactly the same.  I've done = so by=20 looking at the source code, but I can't run it to get the exact SQL = since I=20 don't have the database on my home machine.
 
select s.*
from shipment=20 s
    inner join carrier_code cc on s.carrier_code_id = =3D=20 cc.id
    inner join carrier c = on=20 cc.carrier_id =3D c.id
    inner join = carrier_to_person ctp=20 on ctp.carrier_id =3D c.id
    inner join person p = on p.id =3D=20 ctp.person_id
    inner join = shipment_status cs on=20 s.current_status_id =3D cs.id
    inner join = release_code rc on=20 cs.release_code_id =3D rc.id
    left join = shipment_status ss on=20 ss.shipment_id =3D s.id
where
    p.id =3D = :personId=20 and
    s.is_purged =3D false = and
   =20 rc.number =3D '9' and
    cs is not null=20 and
    cs.date >=3D current_date - 31
order by = cs.date=20 desc
Just assume I have no indexes for the = moment=20 because while some of the indexes I made make it work faster, it's still = around=20 250 milliseconds and under heavy load, the query performs very badly = (6-7=20 seconds).
 
For your information:
 
shipment contains 40,000 = rows
shipment_status contains 80,000 = rows
release_code contains 8 = rows
person contains 300 rows
carrier contains 60 rows
carrier_code contains 70 = rows
 
The filter ratios are:
 
rc.number =3D = '9' (0.125)
cs.date >=3D current_date - 31 = (.10)
p.id =3D ? (0.003)
s.is_purged =3D false = (.98)
 
I really hope someone can help since = I'm pretty=20 much stuck.
 
Best regards and many = thanks,
Ken
------=_NextPart_000_000D_01C51ECA.4F338250--