Re: Curious planning decision

Steve Rogerson <steve(dot)pg(at)yewtc(dot)demon(dot)co(dot)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