From: | Joe Lester <joe_lester(at)sweetwater(dot)com> |
---|---|
To: | pgsql-performance(at)postgresql(dot)org |
Subject: | Index Being Ignored? |
Date: | 2006-06-30 13:31:52 |
Message-ID: | D2575270-BD66-4654-A9E7-11A4207A00D2@sweetwater.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
I have a index question. My table has 800K rows and I a doing a basic
query on an indexed integer field which takes over 2 seconds to
complete because it's ignoring the index for some reason. Any ideas
as to why it's ignoring the index? I'm using postgres 8.0.2.
SELECT count(*) FROM purchase_order_items WHERE expected_quantity > '0'
EXPLAIN ANALYZE reveals that it's not using the index...
Aggregate (cost=22695.28..22695.28 rows=1 width=0) (actual
time=2205.688..2205.724 rows=1 loops=1)
-> Seq Scan on purchase_order_items (cost=0.00..21978.08
rows=286882 width=0) (actual time=0.535..2184.405 rows=7458 loops=1)
Filter: (expected_quantity > 0)
Total runtime: 2207.203 ms
However, if I use the "SET ENABLE_SEQSCAN TO OFF" trick, then it does
use the index and is much faster.
SET ENABLE_SEQSCAN TO OFF;
EXPLAIN ANALYZE SELECT count(*) FROM purchase_order_items WHERE
expected_quantity > '0'
Aggregate (cost=1050659.46..1050659.46 rows=1 width=0) (actual
time=137.393..137.441 rows=1 loops=1)
-> Index Scan using purchase_order_items_expected_quantity_idx on
purchase_order_items (cost=0.00..1049942.25 rows=286882 width=0)
(actual time=0.756..119.990 rows=7458 loops=1)
Index Cond: (expected_quantity > 0)
Total runtime: 139.185 ms
I could understand if this was a really complex query and the planner
got confused... but this is such a simple query. Is it OK to use "SET
ENABLE_SEQSCAN TO OFF;" in production code? Is there another solution?
Thanks!
------------------------------
-- Table Definition --
CREATE TABLE purchase_order_items (
id serial NOT NULL,
purchase_order_id integer,
manufacturer_id integer,
quantity integer,
product_name character varying(16),
short_description character varying(60),
expected_quantity integer,
received_quantity integer,
"position" real,
created_at timestamp without time zone DEFAULT now(),
updated_at timestamp without time zone
);
-- Index --
CREATE INDEX purchase_order_items_expected_quantity_idx ON
purchase_order_items USING btree (expected_quantity);
From | Date | Subject | |
---|---|---|---|
Next Message | Ksenia Marasanova | 2006-06-30 14:13:52 | newly created database makes queries run 300% faster |
Previous Message | Peter Newman | 2006-06-30 07:32:39 | 100% CPU |