X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [64.117.224.130]) by svr1.postgresql.org (Postfix) with ESMTP id A5BEAD1B8A7 for ; Sat, 30 Aug 2003 15:02:15 +0000 (GMT) Received: from svr1.postgresql.org ([64.117.224.193]) by localhost (neptune.hub.org [64.117.224.130]) (amavisd-new, port 10024) with ESMTP id 37496-07 for ; Sat, 30 Aug 2003 12:02:05 -0300 (ADT) Received: from sss.pgh.pa.us (unknown [192.204.191.242]) by svr1.postgresql.org (Postfix) with ESMTP id B2626D1B8C0 for ; Sat, 30 Aug 2003 12:02:04 -0300 (ADT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.12.9/8.12.9) with ESMTP id h7UF21jX024638; Sat, 30 Aug 2003 11:02:01 -0400 (EDT) To: Rob Nagler Cc: pgsql-performance@postgresql.org Subject: Re: How to force Nested Loop plan? In-reply-to: <16208.41885.960000.12706@gargle.gargle.HOWL> References: <16208.41885.960000.12706@gargle.gargle.HOWL> Comments: In-reply-to Rob Nagler message dated "Sat, 30 Aug 2003 07:16:13 -0600" Date: Sat, 30 Aug 2003 11:02:01 -0400 Message-ID: <24637.1062255721@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200308/533 X-Sequence-Number: 3349 Rob Nagler writes: > I'm trying to understand how I can get the planner to always do the > right thing with this query: > SELECT > aa_t.min_date_time > FROM > aa_t > , bb_t > , cc_t > WHERE bb_t.bb_id = aa_t.bb_id > AND aa_t.realm_id = cc_t.realm_id > AND aa_t.server_id = 21 > ORDER BY aa_t.min_date_time desc > LIMIT 1 > OFFSET 674 > -> Index Scan Backward using aa_t20 on aa_t (cost=0.00..76738.77 rows=3454 width=46) (actual time=0.10..31.30 rows=676 loops=1) > Filter: (server_id = 21::numeric) The reason the planner does not much like this plan is that it's estimating that quite a lot of rows will have to be hit in min_date_time order before it finds enough rows with server_id = 21. Thus the high cost estimate for the above step. I suspect that the reason you like this plan is that there's actually substantial correlation between server_id and min_date_time, such that the required rows are found quickly. Before trying to force the planner into what you consider an optimal plan, you had better ask yourself whether you can expect that correlation to hold up in the future. If not, your plan could become pessimal pretty quickly. I'd suggest creating a double-column index: create index aa_ti on aa_t(server_id, min_date_time); and altering the query to read ORDER BY aa_t.server_id DESC, aa_t.min_date_time DESC (you need this kluge to make sure the planner recognizes that the new index matches the ORDER BY request). Then you should get a plan with a much smaller cost coefficient for this step. regards, tom lane PS: does server_id really need to be NUMERIC? Why not integer, or at worst bigint?