From: Tom Lane <tgl@sss.pgh.pa.us>
To: Abhijit Menon-Sen <ams@oryx.com>
Cc: pgsql-performance@postgresql.org
Cc: arnt@oryx.com
Subject: Re: 10x rowcount mis-estimation favouring merge over nestloop
Date: Fri, 10 Nov 2006 01:15:24 -0500
Message-ID: <16547.1163139324@sss.pgh.pa.us> (raw)
In-Reply-To: <20061110051245.GA1928@penne.toroid.org>
References: <20061110051245.GA1928@penne.toroid.org>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: tgl@sss.pgh.pa.us, ams@oryx.com, arnt@oryx.com
Subject: Re: 10x rowcount mis-estimation favouring merge over nestloop
In-Reply-To: <16547.1163139324@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox