To: Abhijit Menon-Sen <ams@oryx.com>
cc: pgsql-performance@postgresql.org, arnt@oryx.com
Subject: Re: 10x rowcount mis-estimation favouring merge over nestloop 
In-reply-to: <20061110051245.GA1928@penne.toroid.org> 
References: <20061110051245.GA1928@penne.toroid.org>
Comments: In-reply-to Abhijit Menon-Sen <ams@oryx.com>
	message dated "Fri, 10 Nov 2006 10:42:45 +0530"
Date: Fri, 10 Nov 2006 01:15:24 -0500
Message-ID: <16547.1163139324@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

Abhijit Menon-Sen <ams@oryx.com> writes:
> The header_fields table contains 13.5M rows, of which only ~250K match
> the where condition. I created an index like this:
>     create index hffpv on header_fields(field)
>     where field<=12 and (part!='' or value ilike '%,%')

> Note the 2M estimated rowcount in the bitmap index scan on header_fields
> vs. the actual number (264180).

I think this is basically a lack-of-column-correlation-stats problem.
The planner is estimating this on the basis of the overall selectivity
of the "field<=12" condition, but it seems that "field<=12" is true for
a much smaller fraction of the rows satisfying (part!='' or value ilike '%,%')
than for the general population of rows in the header_fields table.

There's been some speculation about obtaining stats on partial indexes
as a substitute for solving the general problem of correlation stats,
but I for one don't have a very clear understanding of how it'd work.

			regards, tom lane