On 7 March 2016 at 14:51, Tom Lane <tgl@xxxxxxxxxxxxx> wrote: > Geoff Winkless <pgsqladmin@xxxxxxxx> 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 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
