Обсуждение: 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
On Wed, Oct 22, 2014 at 8:18 AM, Enrico Pirozzi <sscotty71@gmail.com> 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?
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