Slow query not using index
От | Ed L. |
---|---|
Тема | Slow query not using index |
Дата | |
Msg-id | 200403191808.28103.pgsql@bluepolka.net обсуждение исходный текст |
Список | 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 DELETENO ACTION, fk_vendorinterface FOREIGN KEY (vendorinterface_code) REFERENCES vendorinterface(code) ON UPDATENO 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)
В списке pgsql-general по дате отправления: