From: | "Ed L(dot)" <pgsql(at)bluepolka(dot)net> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Slow query not using index |
Date: | 2004-03-20 01:08:28 |
Message-ID: | 200403191808.28103.pgsql@bluepolka.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
I have a slow 7.3.4 query unexpectedly failing to use an index,
doing seq scan instead on 900K rows when it seems it ought to
be able to use index to narrow that by about 99.99% or so.
The table has been recently analyzed. Upgrading to 7.4.* is
not currently an option. Any clues as to how I could identify
the problem from the output below or any other suggestions? TIA.
The table:
===========
% psql -c "\d ab1"
Table "public.ab1"
Column | Type | Modifiers
-----------------------+-----------------------------+------------------------------------------------------
key | integer | not null default nextval('public.ab1_key_seq'::text)
originalab1 | text |
modifiedab1 | text |
transactiontype | character(12) |
posteddatetime | timestamp without time zone |
tobeprocesseddatetime | timestamp without time zone |
processeddatetime | timestamp without time zone |
applicationmessage | character varying(200) |
vendorinterface_code | character(8) | not null
customer_key | integer |
visit_key | integer |
export | boolean |
Indexes: pk_ab1 primary key btree ("key"),
ab1_posteddatetime btree (posteddatetime)
Foreign Key constraints: fk_visit FOREIGN KEY (visit_key) REFERENCES visit("key") ON UPDATE NO ACTION ON DELETE NO ACTION,
fk_customer FOREIGN KEY (customer_key) REFERENCES customer("key") ON UPDATE NO ACTION ON DELETE NO ACTION,
fk_vendorinterface FOREIGN KEY (vendorinterface_code) REFERENCES vendorinterface(code) ON UPDATE NO ACTION ON DELETE NO ACTION
Triggers: ab1_data_retirement_trigger
The slow query:
===============
This query should delete no more than just a few of the 900K rows...
% time psql -c "explain analyze DELETE FROM ab1 WHERE posteddatetime < CAST(now() - '90 days 03:00'::interval AS TIMESTAMP)"
QUERY PLAN
-------------------------------------------------------------------------------------------------------------
Seq Scan on ab1 (cost=0.00..210980.42 rows=308357 width=6) (actual time=17173.01..17173.01 rows=0 loops=1)
Filter: (posteddatetime < ((now() - '90 days 03:00'::interval))::timestamp without time zone)
Total runtime: 17173.15 msec
(3 rows)
real 0m17.821s
user 0m0.010s
sys 0m0.000s
Example run:
============
% time psql -c "DELETE FROM hl7 WHERE posteddatetime < CAST(now() - '90 days 03:00'::interval AS TIMESTAMP)"
DELETE 0
real 0m19.980s
user 0m0.000s
sys 0m0.000s
More explain output:
====================
% psql -c "explain analyze select count(key) from ab1 "
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------
Aggregate (cost=204042.39..204042.39 rows=1 width=4) (actual time=17535.85..17535.85 rows=1 loops=1)
-> Seq Scan on ab1 (cost=0.00..201729.71 rows=925071 width=4) (actual time=0.04..16325.51 rows=908754 loops=1)
Total runtime: 17536.44 msec
(3 rows)
Number of rows actually there:
==============================
% psql -c "select count(key) from ab1 "
count
--------
908755
(1 row)
From | Date | Subject | |
---|---|---|---|
Next Message | David Garamond | 2004-03-20 03:09:03 | Re: Index selection (and partial index) for BYTEA field |
Previous Message | Tom Lane | 2004-03-19 23:55:55 | Re: pg_dump on older version of postgres eating huge |