Thread: explain and index scan

Hi all.

PostgreSQL 9.1.2 on i686-pc-linux-gnu, compiled by gcc-4.4.real (Debian 4.4.5-8) 4.4.5, 32-bit

 id | integer | not null Vorgabewert nextval('a_id_seq'::regclass)
 a  | integer | not null
 b  | integer | not null
Indexe:
    "a_pkey" PRIMARY KEY, btree (id)
    "a_a_key" UNIQUE CONSTRAINT, btree (a, b)

explain select id from a where a = 1 and b = -90875 ;
                                           QUERY PLAN
-------------------------------------------------------------------------------------------------
 Index Scan using a_a_key on a  (cost=0.00..2.37 rows=1 width=4)
   Index Cond: ((a = 1) AND (b = (-90875)))

 explain select id from a where b = -90875 ;
                                            QUERY PLAN
---------------------------------------------------------------------------------------------------
 Index Scan using a_a_key on a  (cost=0.00..961.76 rows=1 width=4)
   Index Cond: (b = (-90875))

Both select where shown as 'Index Scan'. But the second select is not a real index scan,
its more a seq scan on an index, i think. I think, it would be a good idea to show this in the
explain. Now you can see this only if you look at the cost.

Best regards and thank you for your work,
Andreas

Andreas wrote:
> PostgreSQL 9.1.2 on i686-pc-linux-gnu, compiled by gcc-4.4.real
(Debian 4.4.5-8) 4.4.5, 32-bit
>
>  id | integer | not null Vorgabewert nextval('a_id_seq'::regclass)
>  a  | integer | not null
>  b  | integer | not null
> Indexe:
>     "a_pkey" PRIMARY KEY, btree (id)
>     "a_a_key" UNIQUE CONSTRAINT, btree (a, b)
>
> explain select id from a where a = 1 and b = -90875 ;
>                                            QUERY PLAN
>
------------------------------------------------------------------------
-------------------------
>  Index Scan using a_a_key on a  (cost=0.00..2.37 rows=1 width=4)
>    Index Cond: ((a = 1) AND (b = (-90875)))
>
>  explain select id from a where b = -90875 ;
>                                             QUERY PLAN
>
------------------------------------------------------------------------
---------------------------
>  Index Scan using a_a_key on a  (cost=0.00..961.76 rows=1 width=4)
>    Index Cond: (b = (-90875))
>
> Both select where shown as 'Index Scan'. But the second select is not
a real index scan,
> its more a seq scan on an index, i think. I think, it would be a good
idea to show this in the
> explain. Now you can see this only if you look at the cost.

A full scan of the index is also an index scan.

I think that it might be justified to make a difference here if
PostgreSQL
scanned full indexes routinely.  But this is not the case: index scans
are
normally only considered if they are estimated to hit only a small
percentage of the rows.

I think that your example is pathological, and the only way I could
reproduce it is by setting enable_seqscan=off.

How were the enable_* parameters set when you ran your example?
What is the output of
  SELECT * FROM pg_stats WHERE tablename='a';

Yours,
Laurenz Albe

"Albe Laurenz" <laurenz.albe@wien.gv.at> writes:
> Andreas wrote:
>> Both select where shown as 'Index Scan'. But the second select is not
> a real index scan,

> A full scan of the index is also an index scan.

Yes.  In particular it won't visit the heap for rows that don't satisfy
the index condition.  So as long as the index is a good deal smaller
than the heap, this sort of plan is entirely sensible.

> I think that your example is pathological, and the only way I could
> reproduce it is by setting enable_seqscan=off.

I'm thinking he's got random_page_cost = 1.

            regards, tom lane