On 7 March 2016 at 16:44, Tom Lane <tgl@xxxxxxxxxxxxx> wrote: > Geoff Winkless <pgsqladmin@xxxxxxxx> writes: >> But as far as I can see, apart from the absolute extremes, the >> index-only scan is _always_ going to be quicker than the index+table >> scan. > > Well, that is a different issue: what does the planner think of an > index-only scan as compared to a regular index scan. I suspect that > it's pricing the IOS very high because a lot of the table is dirty > and therefore will have to be visited even in a nominally index-only > scan. You might check whether the plan choice changes immediately > after a VACUUM of the table. I ran VACUUM FULL and VACUUM ANALYZE. It made no difference. I would have thought that if it were the case then the equality-test queries would suffer from the same problem anyway, no? Even being fairly kind and selecting an scdate range that's only 1% into the set the query takes over 4 times the amount of time taken by the indexed query - so the "best" range for the index+table method is utterly tiny - it would be reasonable only when the scdate field is uniformly distributed, which even in a table without correlation between the fields is likely to be almost never. Geoff -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
