X-Original-To: pgsql-performance-postgresql.org@postgresql.org Received: from localhost (mx1.hub.org [200.46.208.251]) by postgresql.org (Postfix) with ESMTP id D9BE59FA5BA for ; Wed, 28 Jun 2006 11:37:31 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024) with ESMTP id 77323-07 for ; Wed, 28 Jun 2006 11:37:26 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey- Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by postgresql.org (Postfix) with ESMTP id 8456C9F9DCC for ; Wed, 28 Jun 2006 11:37:26 -0300 (ADT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.13.6/8.13.6) with ESMTP id k5SEbOwc015073; Wed, 28 Jun 2006 10:37:24 -0400 (EDT) To: Andrew Sagulin cc: pgsql-performance@postgresql.org Subject: Re: Large index scan perfomance and indexCorrelation (PG 8.1.4 Win32) In-reply-to: <624168784.20060628133355@yandex.ru> References: <463024718.20060627161408@yandex.ru> <1151445858.2479.170.camel@localhost.localdomain> <624168784.20060628133355@yandex.ru> Comments: In-reply-to Andrew Sagulin message dated "Wed, 28 Jun 2006 13:33:55 +0400" Date: Wed, 28 Jun 2006 10:37:24 -0400 Message-ID: <15072.1151505444@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200606/485 X-Sequence-Number: 19842 Andrew Sagulin writes: > Does PostgreSQL's development team plan to revise the index scan > cost algorithm or issues like mine is too rare for taking into account? The algorithm is certainly open for discussion, but we're not changing it on the basis of just a single report ... You're mistaken to be fingering min_IO_cost as the source of the issue, because there is also a correction for near-sequential access in cost_bitmap_heap_scan. If we were to bias the system as heavily against the consideration as you propose, we would logically have to put a similar bias into cost_bitmap_heap_scan, and you'd probably still end up with a plain indexscan. What you need to do is compare the two functions and figure out what part of the cost models are out of line with reality. I tend to agree with the upthread comment that the nonlinear interpolation between min_IO_cost and max_IO_cost is suspect ... but that may or may not have anything truly to do with your problem. It might be that cost_index is fine and cost_bitmap_heap_scan is overcharging. BTW there are already some changes in HEAD relating to this, please see the pghackers archives from beginning of June (thread "More thoughts about planner's cost estimates"). regards, tom lane