Slow query not using index

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)

Browse pgsql-general by date

  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