Re: index and seq scan

In the first query explain thinks it is going to match 81050 rows and
the planner thinks that this is a large enough number that a
sequential scan would be a win. In the second query (which I am
guessing you mis-pasted as it still says 'appid > 10') explain thinks
it will only be returning 57 rows, and so it uses a index scan.

If these values don't look like they are close to reality, then you
need to vacuum analyze your table. Otherwise you need to see if the
first query would actually be faster using an index scan. Type:

SET enable_seqscan TO off;

at the psql prompt, and then run your query again.

I know from my own experience that sometimes PostgreSQL way too
conservative about using index scans. Fortunately it has gotten quite
a bit better, and 7.2 promises to be better still. And if worse comes
to worse, it is always possible to force PostgreSQL to use an index
scan.

Jason

Tina Messmann <tina(dot)messmann(at)xinux(dot)de> writes:

> 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?
>
> 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