From: | "Gregory Wood" <gregw(at)com-stock(dot)com> |
---|---|
To: | "PostgreSQL-General" <pgsql-general(at)postgresql(dot)org> |
Subject: | Yet another "Why won't PostgreSQL use my index?" |
Date: | 2002-06-20 16:05:53 |
Message-ID: | 001501c21874$5c604080$7889ffcc@comstock.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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
From | Date | Subject | |
---|---|---|---|
Next Message | Stephan Szabo | 2002-06-20 16:16:41 | Re: Serious Crash last Friday |
Previous Message | Ned Lilly | 2002-06-20 15:47:21 | Re: ERWin 3.5.2 and Postgres ODBC |