On Wed, Oct 22, 2014 at 8:18 AM, Enrico Pirozzi <sscott...@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
