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 A07E140C005 for ; Sat, 29 Jan 2005 22:44:39 +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 32566-03 for ; Sat, 29 Jan 2005 22:44:34 +0000 (GMT) Received: from fep1.cogeco.net (smtp.cogeco.net [216.221.81.25]) by svr1.postgresql.org (Postfix) with ESMTP id 2A61E40C003 for ; Sat, 29 Jan 2005 22:44:33 +0000 (GMT) Received: from windsortransportationclub.com (d57-59-156.home.cgocable.net [24.57.59.156]) by fep1.cogeco.net (Postfix) with SMTP id 3164C619E for ; Sat, 29 Jan 2005 17:44:34 -0500 (EST) Received: (qmail 26711 invoked from network); 29 Jan 2005 23:14:21 -0000 Received: from d36-66-205.home1.cgocable.net (HELO a96dfxb4kjzogw) (24.36.66.205) by 0 with SMTP; 29 Jan 2005 23:14:21 -0000 Message-ID: <002201c50654$1a8c50b0$cd422418@a96dfxb4kjzogw> From: "Ken Egervari" To: "PFC" , References: <001601c50416$57090e10$cd422418@a96dfxb4kjzogw> Subject: Re: Performance problem with semi-large tables Date: Sat, 29 Jan 2005 17:44:33 -0500 MIME-Version: 1.0 Content-Type: text/plain; format=flowed; charset="iso-8859-15"; reply-type=response Content-Transfer-Encoding: 7bit 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.422 tagged_above=0 required=5 tests=AWL, FORGED_RCVD_HELO, RCVD_IN_NJABL_DUL, RCVD_IN_SORBS_DUL X-Spam-Level: * X-Archive-Number: 200501/616 X-Sequence-Number: 10306 > Well, postgres does what you asked. It will be slow, because you have a > full table join. LIMIT does not change this because the rows have to be > sorted first. I am aware that limit doesn't really affect the execution time all that much. It does speed up ORM though and keeps the rows to a manageable list so users don't have to look at thousands, which is good enough for me. My intention here is that the date was supposed to be a good filter. > The date is in shipment_status so you should first get the > shipment_status.id that you need and later join to shipment. This will > avoid the big join : > > > SELECT s.*, ss.* FROM > (SELECT * FROM shipment_status WHERE release_code_id IN > (SELECT r.id FROM release_code WHERE r.filtered_column = '5') > ORDER BY date DESC LIMIT 100 > ) as ss, shipment s > WHERE s.current_status_id = ss.id > ORDER BY date DESC LIMIT 100 > > Is this better ? This looks like it might be what I want. It's not that I was not aware of the correct join order. I used Dan Tow's diagram method and learned that filtering on date first is the best approach, then releae code, then finally shipment for this particular query. I just didn't know how to tell PostgreSQL how to do this. So are you suggesting as a general rule then that sub-queries are the way to force a specific join order in postgres? If that is the case, I will do this from now on.