Thread: Index scan vs indexonly scan method

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?

My develop PostgreSQL version is a 9.4 beta

regards,
Enrico

--
That's one small step for man; one giant leap for mankind

Enrico Pirozzi
Tel.  +39 0861 1855771 - Mob.+39 328 4164437 - Fax  +39 0861 1850310
http://www.pgtraining.com <http://www.pgtraining.com-> -
info@pgtraining.com
www.enricopirozzi.info - info@enricopirozzi.info
PEC: enrico.pirozzi@pec.it
Skype sscotty71 - Gtalk sscotty71@gmail.com

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.klaver@aklaver.com

> 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.
>
>
Thank you for your answer

Enrico


--
That's one small step for man; one giant leap for mankind

Enrico Pirozzi
Tel.  +39 0861 1855771 - Mob.+39 328 4164437 - Fax  +39 0861 1850310
http://www.pgtraining.com <http://www.pgtraining.com-> -
info@pgtraining.com
www.enricopirozzi.info - info@enricopirozzi.info
PEC: enrico.pirozzi@pec.it
Skype sscotty71 - Gtalk sscotty71@gmail.com