From: | Geoff Winkless <pgsqladmin(at)geoff(dot)dj> |
---|---|
To: | "Peter J(dot) Holzer" <hjp-pgsql(at)hjp(dot)at>, Postgres General <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: index problems (again) |
Date: | 2016-03-08 10:16:57 |
Message-ID: | CAEzk6ffdcL+CBU8EkM3pmj5yGSfdm9mSr9NnX+E97=x1ns62nw@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On 7 March 2016 at 20:40, Peter J. Holzer <hjp-pgsql(at)hjp(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | Berend Tober | 2016-03-08 10:50:02 | Re: Logger into table and/or to cli |
Previous Message | Andreas Joseph Krogh | 2016-03-08 09:53:39 | Exclude pg_largeobject form pg_dump |