Yet another "Why won't PostgreSQL use my index?"
От | Gregory Wood |
---|---|
Тема | Yet another "Why won't PostgreSQL use my index?" |
Дата | |
Msg-id | 001501c21874$5c604080$7889ffcc@comstock.com обсуждение исходный текст |
Ответы |
Re: Yet another "Why won't PostgreSQL use my index?"
Re: Yet another "Why won't PostgreSQL use my index?" Re: Yet another "Why won't PostgreSQL use my index?" |
Список | pgsql-general |
Trying to use a single column index on a somewhat large table (1.9M rows), and PostgreSQL really doesn't want to. It estimates the number of rows at 12749 (actual 354), which is only .6% of the table... well within reasonable index range I would think. And yes, I've run an analyze on the table. Here are the queries I've run: =============== cns=# analyze re_site_listings_index; ANALYZE cns=# select count(1) from re_site_listings_index; count --------- 1906455 (1 row) cns=# explain analyze select * from re_site_listings_index where idx_siteid=237; NOTICE: QUERY PLAN: Seq Scan on re_site_listings_index (cost=0.00..41050.76 rows=12749 width=302) (actual time=158.57..2839.78 rows=354 loops=1) Total runtime: 2841.60 msec EXPLAIN cns=# set enable_seqscan=false; SET VARIABLE cns=# explain analyze select * from re_site_listings_index where idx_siteid=237; NOTICE: QUERY PLAN: Index Scan using bill_idx_siteid on re_site_listings_index (cost=0.00..48402.08 rows=12749 width=302) (actual time=0.30..3.95 rows=354 loops=1) Total runtime: 5.76 msec EXPLAIN cns=# select version(); version --------------------------------------------------------------------- PostgreSQL 7.2.1 on i386-unknown-freebsd4.3, compiled by GCC 2.95.3 (1 row) =============== I *think* that's all the relevant information... please let me know if I forgot anything. Greg
В списке pgsql-general по дате отправления: