Re: 10x rowcount mis-estimation favouring merge over nestloop - Mailing list pgsql-performance

From Tom Lane
Subject Re: 10x rowcount mis-estimation favouring merge over nestloop
Date
Msg-id 16547.1163139324@sss.pgh.pa.us
Whole thread Raw
In response to 10x rowcount mis-estimation favouring merge over nestloop  (Abhijit Menon-Sen <ams@oryx.com>)
Responses Re: 10x rowcount mis-estimation favouring merge over nestloop  (Abhijit Menon-Sen <ams@oryx.com>)
List pgsql-performance
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

pgsql-performance by date:

Previous
From: Abhijit Menon-Sen
Date:
Subject: 10x rowcount mis-estimation favouring merge over nestloop
Next
From: Abhijit Menon-Sen
Date:
Subject: Re: 10x rowcount mis-estimation favouring merge over nestloop