I think maybe I am making a dumb mistake here, so apologies in advance. But I have a query that is not using existing indices and I do not understand why. Am I reading the plan wrong? Are the indices wrong somehow?
The table is
mustang=# \d+ bss.amplifier_saturation
Table "bss.amplifier_saturation"
Column | Type | Modifiers | Storage | Description
--------+--------------------------+-------------------------------------------------------------------+---------+-------------
value | integer | not null | plain |
target | integer | not null | plain |
start | timestamp with time zone | not null | plain |
end | timestamp with time zone | not null | plain |
id | integer | not null default nextval('amplifier_saturation_id_seq'::regclass) | plain |
lddate | timestamp with time zone | not null default now() | plain |
Indexes:
"amplifier_saturation_pkey" PRIMARY KEY, btree (id)
"amplifier_saturation_target_start_end_key" UNIQUE CONSTRAINT, btree (target, start, "end")
"amplifier_saturation_end" btree ("end")
"amplifier_saturation_lddate" btree (lddate)
"amplifier_saturation_start" btree (start)
"amplifier_saturation_target" btree (target)
"amplifier_saturation_value" btree (value)
Has OIDs: no
and the query/plan is:
mustang=# explain select max(lddate) from bss.amplifier_saturation where start >= '1987-12-31 00:00:00' and start <= '1988-04-09 00:00:00';
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------
Result (cost=189.41..189.42 rows=1 width=0)
InitPlan 1 (returns $0)
-> Limit (cost=0.00..189.41 rows=1 width=8)
-> Index Scan Backward using amplifier_saturation_lddate on amplifier_saturation (cost=0.00..2475815.50 rows=13071 width=8)
Index Cond: (lddate IS NOT NULL)
Filter: ((start >= '1987-12-31 00:00:00-08'::timestamp with time zone) AND (start <= '1988-04-09 00:00:00-07'::timestamp with time zone))
(6 rows)
My question is: why does this not use the index amplifier_saturation_start? It seems to me like the DB should scan that to find the start date, and then continue through to delimit all entries 'til the end date, and finally sort that (small subset of) data for the maximum lddate (something like pp40-41 of SQL Performance Explained).
(I also tried an index on (start, start desc) in desperation but it didn't help).
Aucun commentaire:
Enregistrer un commentaire