X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id 75F3CD1BAF5 for ; Tue, 24 Feb 2004 22:16:09 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 13734-10 for ; Tue, 24 Feb 2004 18:16:09 -0400 (AST) Received: from sss.pgh.pa.us (unknown [192.204.191.242]) by svr1.postgresql.org (Postfix) with ESMTP id F3351D1B982 for ; Tue, 24 Feb 2004 18:16:06 -0400 (AST) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.12.11/8.12.11) with ESMTP id i1OMG90V027172; Tue, 24 Feb 2004 17:16:09 -0500 (EST) To: Kevin Brown Cc: Postgres Performance Subject: Re: Column correlation drifts, index ignored again In-reply-to: <20040224201419.GD3090@filer> References: <200402221617.27658.josh@agliodbs.com> <20040224035601.GC3090@filer> <200402240859.32312.josh@agliodbs.com> <23533.1077647386@sss.pgh.pa.us> <20040224201419.GD3090@filer> Comments: In-reply-to Kevin Brown message dated "Tue, 24 Feb 2004 12:14:20 -0800" Date: Tue, 24 Feb 2004 17:16:08 -0500 Message-ID: <27171.1077660968@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200402/291 X-Sequence-Number: 5815 Kevin Brown writes: > One problem I've been running into is the merge join spilling to disk > because sort_mem isn't big enough. The problem isn't that this is > happening, it's that I think the planner is underestimating the impact > that doing this will have on the time the merge join takes. Does the > planner even account for the possibility that a sort or join will spill > to disk? Yes it does. I thought it was making a pretty good estimate, actually. The only obvious hole in the assumptions is * The disk traffic is assumed to be half sequential and half random * accesses (XXX can't we refine that guess?) Because of the way that tuplesort.c works, the first merge pass should be pretty well sequential, but I think the passes after that might be mostly random from the kernel's viewpoint :-(. Possibly the I/O cost should be adjusted depending on how many merge passes we expect. > In any case, one thing that none of this really accounts for is that > it's better to set random_page_cost too low than too high. That depends on what you are doing, although I will concede that a lot of people are doing things where indexscans should be favored. regards, tom lane