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 9B58840BFB8 for ; Sat, 29 Jan 2005 22:06:46 +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 26762-09 for ; Sat, 29 Jan 2005 22:06:37 +0000 (GMT) Received: from boutiquenumerique.com (boutiquenumerique.com [82.67.9.10]) by svr1.postgresql.org (Postfix) with ESMTP id 116DC40BF99 for ; Sat, 29 Jan 2005 22:06:36 +0000 (GMT) Received: (qmail 28327 invoked from network); 29 Jan 2005 23:06:57 +0100 Received: from unknown (HELO musicbox) (boutiquenumerique-lists@192.168.0.2) by boutiquenumerique.com with SMTP; 29 Jan 2005 23:06:57 +0100 To: "Ken Egervari" , pgsql-performance@postgresql.org Subject: Re: Performance problem with semi-large tables References: <001601c50416$57090e10$cd422418@a96dfxb4kjzogw> Message-ID: Date: Sat, 29 Jan 2005 23:08:58 +0100 From: PFC Organization: =?iso-8859-15?Q?La_Boutique_Num=E9rique?= Content-Type: text/plain; format=flowed; delsp=yes; charset=iso-8859-15 MIME-Version: 1.0 Content-Transfer-Encoding: 8bit In-Reply-To: <001601c50416$57090e10$cd422418@a96dfxb4kjzogw> User-Agent: Opera M2/7.54 (Linux, build 751) X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.003 tagged_above=0 required=5 tests=AWL X-Spam-Level: X-Archive-Number: 200501/614 X-Sequence-Number: 10304 > select s.*, ss.* > from shipment s, shipment_status ss, release_code r > where s.current_status_id = ss.id > and ss.release_code_id = r.id > and r.filtered_column = '5' > order by ss.date desc > limit 100; > Release code is just a very small table of 8 rows by looking at the > production data, hence the 0.125 filter ratio. However, the data > distribution is not normal since the filtered column actually pulls out > about 54% of the rows in shipment_status when it joins. Postgres seems > to be doing a sequencial scan to pull out all of these rows. Next, it > joins approx 17550 rows to shipment. Since this query has a limit, it > only returns the first 100, which seems like a waste. 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. 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 ?