Index scan vs indexonly scan method

[Enrico Pirozzi]

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. [phone] - Mob.[phone] - Fax [phone]
http://www.pgtraining.com <http://www.pgtraining.com-> -
***@pgtraining.com
www.enricopirozzi.info - ***@enricopirozzi.info
PEC: ***@pec.it
Skype sscotty71 - Gtalk ***@gmail.com

[Adrian Klaver]

Post by Enrico Pirozzi
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
select field1 from table A
where A.field2 <= '2014-08-13 10:20:59.99648+02'
order by 1 desc limit 100
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.

Post by Enrico Pirozzi
My develop PostgreSQL version is a 9.4 beta
regards,
Enrico

[Enrico Pirozzi]

Post by Adrian Klaver
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. [phone] - Mob.[phone] - Fax [phone]
http://www.pgtraining.com <http://www.pgtraining.com-> -
***@pgtraining.com
www.enricopirozzi.info - ***@enricopirozzi.info
PEC: ***@pec.it
Skype sscotty71 - Gtalk ***@gmail.com

[Jeff Janes]

Post by Enrico Pirozzi
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
select field1 from table A
where A.field2 <= '2014-08-13 10:20:59.99648+02'
order by 1 desc limit 100
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?

Yes, that is strange. Are they using scans over the same index?

PostgreSQL never demotes an index-only to a regular scan just because it
might not be worthwhile to do it in index only mode. If it uses a scan on
a index which it recognizes as being eligible for index-only, it will use
it as index-only.

Without seeing the actual EXPLAIN output, it is hard to say more.

Cheers,

Jeff