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