Re: Index scan vs indexonly scan method

On 10/22/2014 08:18 AM, Enrico Pirozzi wrote:
> Hi,
> I was working on this simple query
>
> select field1 from table A
> where A.field2 <= some_value
> order by 1 desc limit some_value
>
> and I saw that:
>
> 1) the planner on this query uses an index only scan method:
>
> select field1 from table A
> where A.field2 <= '2014-08-13 10:20:59.99648+02'
> order by 1 desc limit 100
>
> 2) the planner on this query uses a classic index scan method:
>
> select field1 from table A
> where A.field2 <= '2014-08-13 10:20:59.99648+02'
> order by 1 desc limit 1
>
> the only difference between the two queries is the limit clause,
> for the first query the limit is 100 and for the second the limit is 1
>
> it seems a little bit strange...someone can help me to understand why?

The background on index-only scans:

https://wiki.postgresql.org/wiki/What%27s_new_in_PostgreSQL_9.2#Index-only_scans

In either case(index, index-only) the index has to be scanned. The
difference is where the data is pulled from. In the index-only scan the
query still needs to consult the visibility map to determine whether the
tuple pointed to by the index entry is visible. I would say that in the
limit 1 case the planner determines it is just as easy to check and pull
the data from the actual tuple as to to check the visibility map. In the
limit 100 case it becomes more cost effective to use the visibility map
and pull data directly from the index data.

>
> My develop PostgreSQL version is a 9.4 beta
>
> regards,
> Enrico
>

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com