On 7 March 2016 at 14:51, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Geoff Winkless <pgsqladmin@geoff.dj> writes:
>> So it seems that it should in fact be usable after all. So I'm still
>> stumped as to why the (scdate,sc_id) index isn't used :(
>
> Because the other way is estimated to be cheaper. The estimate is
> wrong, because it's based on a statistical assumption that's wrong
> (ie that sc_id and scdate are uncorrelated), but it's what we have
> to work with at the moment.
Are you saying that the planner can't tell without scanning the index
how much of the index the range constraint will retrieve? That's
reasonable, I suppose, but if you consider the relative size of the
index (92MB) and table (1.6GB) (both of which pieces of information
are available to the planner at query-time) if I were to scan 3% of
the table (which we assume the planner is estimating because of the
cardinality of the scdate field) I've read as much data from disk as
I've read for 50% of the index. That's ignoring the reads I'll have to
do from the sc_id index too... so in the worst-case where I've had to
read the entire index (because the range didn't actually restrict any
records) I'm still only 2x the average-case of the other way. Whereas
the worst-case of the sc_id-only-index-plus-table-retrieve is about
1000x the worst case of the index-only scan.
> select min((select min(sc_id) from legs where scdate = gs))
> from generate_series(20160219, 20160221) gs
> This would only work well for relatively small ranges of scdate,
As it happens it works for the full range of scdate and returns in 99ms.
# select min((select min(sc_id) from legs where scdate = gs))
from generate_series(20150101, 20160303) gs;
min
----------
12914746
(1 row)
Time: 99.210 ms
> but if you had a large range then I think the original plan
> would've been fine.
Well yes, obviously doing MIN() across the whole range is going to be
able to just return as soon as it gets the first value from sc_id and
references the table to check the date; however even in that _best_
case the value comes back in 25ms, ie the _best-case_
index-plus-table-scan is 1/3 the time of the worst-case index-only
scan.
I accept that this is how the planner behaves, but I don't accept that
it's optimal.
Geoff