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
