Why index used/not used
От | Anton Maksimenkov |
---|---|
Тема | Why index used/not used |
Дата | |
Msg-id | 40FDF856.30207@hlebprom.ru обсуждение исходный текст |
Ответы |
Re: Why index used/not used
Re: Why index used/not used |
Список | 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
В списке pgsql-general по дате отправления: