Re: explain and index scan

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