Re: Index scan vs indexonly scan method

On Wed, Oct 22, 2014 at 8:18 AM, Enrico Pirozzi <sscotty71(at)gmail(dot)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