On 7 March 2016 at 20:40, Peter J. Holzer <hjp-pgsql@hjp.at> wrote:
> As Tom wrote, the estimate of having to read only about 140 rows is only
> valid if sc_id and sc_date are uncorrelated. In reality your query has
> to read a lot more than 140 rows, so it is much slower.
But as I've said previously, even if I do select from scdate values
that I know to be in the first 1% of the data (supposedly the perfect
condition) the scan method is insignificantly quicker than the index
(scdate,scid) method.
Even with the absolute perfect storm (loading in the entire index for
the full range) it's still not too bad (1.3 seconds or so).
The point is that to assume, knowing nothing about the data, that the
data is in an even distribution is only a valid strategy if the worst
case (when that assumption turns out to be wildly incorrect) is not
catastrophic. That's not the case here.
Geoff