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
>