Re: Why index used/not used

hello,

Try reading through the pgsql-performance mailing list. Generally the database needs to be vacuumed and analyzed to update the stats usually for the planner to make the correct choices.

Mike

On Wed, Jul 21, 2004 at 11:00:06AM +0600, Anton Maksimenkov wrote:
> Hello.
>
> Explain.
> I have table "traf_raw" contains field "sip_id" (integer). This field
> indexed with "CREATE INDEX traf_raw_sip ON traf_raw (sip_id)".
>
> Question.
> When I try to get different rows postgres use index with one "sip_id"
> and not use index with another "sip_id". I don't understand why it is
> happen, but with more complex queries Seq Scan is so slowly.
>
> Example.
> With "sip_id='19'" there many rows in table, with "sip_id='29'" there
> is no rows.
>
> cnupm=> ANALYZE traf_raw;
> ANALYZE
> cnupm=> EXPLAIN ANALYZE SELECT * FROM traf_raw WHERE sip_id='19' LIMIT
> 10 OFFSET 100000;
> QUERY PLAN
>
> ---------------------------------------------------------------------------------------------------------------------
> Limit (cost=5230.95..5230.99 rows=1 width=56) (actual
> time=2505.89..2505.89 rows=0 loops=1)
> -> Seq Scan on traf_raw (cost=0.00..5230.99 rows=10808 width=56)
> (actual time=0.04..2490.02 rows=10977 loops=1)
> Filter: (sip_id = 19)
> Total runtime: 2505.95 msec
> (4 rows)
>
> cnupm=> EXPLAIN ANALYZE SELECT * FROM traf_raw WHERE sip_id='29' LIMIT
> 10 OFFSET 100000;
> QUERY PLAN
>
> ---------------------------------------------------------------------------------------------------------------------------------
> Limit (cost=391.39..392.70 rows=1 width=56) (actual time=43.08..43.08
> rows=0 loops=1)
> -> Index Scan using traf_raw_sip on traf_raw (cost=0.00..392.70
> rows=99 width=56) (actual time=43.07..43.07 rows=0 loops=1)
> Index Cond: (sip_id = 29)
> Total runtime: 43.16 msec
> (4 rows)
>
> --
> engineer
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: the planner will ignore your desire to choose an index scan if your
> joining column's datatypes do not match