Re: index and seq scan

On Thu, 10 Jan 2002, Tina Messmann wrote:

> Hello List,
>
> i have the following table with an index on appid:
>
> dbl=# \d test
> Table "test"
> Attribute | Type | Modifier
>
> -----------+--------------------------+-------------------------------------------------
> id | integer | not null default
> nextval('"test_id_seq"'::text)
> time | timestamp with time zone |
> appid | integer |
> Indices: appid_idx,
> test_id_key
>
> dbl=# \d appid_idx
> Index "appid_idx"
> Attribute | Type
> -----------+---------
> appid | integer
> btree
>
> Ii want this index to be used in my query, but only the seq. scan is
> used and i don't know why.
> When changing the WHERE expression to 'appid < 10', the index is used
> (see EXPLAIN command below).
> Could someone please explain this behavior to me and how i can use the
> index in the first query?

Have you run vacuum analyze on this table?
It looks to me that the estimates seem to say almost all of the rows
have appid>10. In general if the estimates are correct (over 99% of
the table returned) the index scan is likely to be more expensive in
io than the sequence scan. If you run the query with
enable_seqscan set to false and without, what are the runtimes?

>
> dbl=# explain select * from test where appid > 10;
> NOTICE: QUERY PLAN:
> Seq Scan on test (cost=0.00..1530.84 rows=81050 width=16)
> EXPLAIN
>
> db=# explain select * from test where appid > 10;
> NOTICE: QUERY PLAN:
> Index Scan using appid_idx on test (cost=0.00..70.20 rows=57 width=16)
> EXPLAIN
>
> regards
> Tina
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/users-lounge/docs/faq.html
>