Yet another "Why won't PostgreSQL use my index?"

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

Responses

Browse pgsql-general by date

  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