Re: [GENERAL] Curious planning decision

Steve Rogerson <steve.pg@yewtc.demon.co.uk> writes:
> The date is "today" at the time of writing. The table is analyzed, to pg
> should "know" that date1 <= "today" hence there is no data beyond today.

It doesn't seem to, given this:

>                ->  Bitmap Index Scan on ctable_date1  (cost=0.00..22534.71
> rows=598419 width=0) (actual time=5.204..5.204 rows=37536 loops=1)
>                      Index Cond: (date1 >= '2017-08-10'::date)

You should try just "explain analyze select * from ctable c
where c.date1 >= '2017-08-10'" to confirm that that row estimate
is wrong in isolation, but it looks like it is off by more than 10X.

> ctable has about 60,000,000 rows.

So basically, the planner is estimating 1% selectivity for this condition,
when the true figure is more like 0.0625%.  I find this unsurprising if
you're using the default statistics target of 100 --- that means that the
accuracy of histogram-related predictions can't be expected to be any
better than 1%.  If you crank up the stats target (for this column, or
the whole table, or globally) and re-analyze, the estimate should get
better, and then you should get a better plan.

            regards, tom lane