Re: index problems (again)

On Mon, Mar 7, 2016 at 9:35 AM, Geoff Winkless <pgsqladmin(at)geoff(dot)dj> wrote:
> On 7 March 2016 at 16:44, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> Geoff Winkless <pgsqladmin(at)geoff(dot)dj> 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?

No. The range case scans the entire date range, visits the table for
each row in that range (to check visibility), and takes the min over
the sc_ids which pass the visibility check.

The equality test case jumps directly to the lowest sc_id for the
given scdate, and then has to walk up the sc_ids only until it finds
one which passes the visibility check. Once it finds one which is
visible, it is done with that scdate.

Assuming most tuples are visible, that is a huge difference in the
amount of table blocks being visited. (And maybe index blocks as
well)

Cheers,

Jeff