Why index used/not used

From: Anton Maksimenkov <engineer(at)hlebprom(dot)ru>
To: pgsql-general(at)postgresql(dot)org
Subject: Why index used/not used
Date: 2004-07-21 05:00:06
Message-ID: 40FDF856.30207@hlebprom.ru
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Mike G 2004-07-21 05:06:09 Re: pgadmin 3
Previous Message Gunasekaran Balakrishnan 2004-07-21 04:06:18 Re: DIfferent ORDER BY behaviour in 7.4.2