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.
