Unused index in range of dates query (PostgreSQL, timestamp with time zone)

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).

edit 1:

mustang=# explain select count(*) from bss.amplifier_saturation
where start >= '1987-12-31 00:00:00'
and   start <= '1988-04-09 00:00:00';
                                                                      QUERY PLAN                                                                       
-------------------------------------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=38711.84..38711.85 rows=1 width=0)
   ->  Index Scan using amplifier_saturation_start on amplifier_saturation  (cost=0.00..38681.47 rows=12146 width=0)
         Index Cond: ((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))
(3 rows)

(and running analyze hasn't helped. and pg_stats shows a reasonable spread of values for start which seems to argue for using the index. and setting statistics to 10,000 on either column (start or lddate) didn't help).

Maybe I should explain why I think the plan is wrong. The table contains 30,000,000 rows. Only 3,500 are in the date range. But maybe that's still too many for them to be read separately?

edit 2:

OK, adding an index on (lddate desc, start) works (not sure the desc is required). It can then use a pure index approach (IIUC) (and it runs much faster):

mustang=# create index tmp_as on bss.amplifier_saturation (lddate desc, start);
CREATE INDEX
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=69.76..69.77 rows=1 width=0)
   InitPlan 1 (returns $0)
     ->  Limit  (cost=0.00..69.76 rows=1 width=8)
           ->  Index Scan using tmp_as on amplifier_saturation  (cost=0.00..861900.22 rows=12356 width=8)
                 Index Cond: ((lddate IS NOT NULL) AND (start >= '1987-12-31 00:00:00-08'::timestamp with time zone) AND (start <= '1988-04-09 00:00:00-07'::timestamp with ti
me zone))
(5 rows)

I'll post an answer myself in a while, but am waiting in case someone smarter than me comes along...

Unused index in range of dates query

I have a query that is not using existing indices and I do not understand why.

The table:

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)

The query/plan:

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))

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.

mustang=# explain select count(*) from bss.amplifier_saturation
where start >= '1987-12-31 00:00:00'
and   start <= '1988-04-09 00:00:00';
                                                                      QUERY PLAN                                                                       
-------------------------------------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=38711.84..38711.85 rows=1 width=0)
   ->  Index Scan using amplifier_saturation_start on amplifier_saturation  (cost=0.00..38681.47 rows=12146 width=0)
         Index Cond: ((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))

• Running ANALYZE didn't help.
• pg_stats shows a reasonable spread of values for start which seems to argue for using the index.
• Setting statistics to 10,000 on either column (start or lddate) didn't help.

Maybe I should explain why I think the plan is wrong. The table contains 30,000,000 rows. Only 3,500 are in the date range. But maybe that's still too many for them to be read separately?

Adding an index on (lddate desc, start) works (not sure the desc is required). It can then use a pure index approach (IIUC) and it runs much faster:

mustang=# create index tmp_as on bss.amplifier_saturation (lddate desc, start);
CREATE INDEX
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=69.76..69.77 rows=1 width=0)
   InitPlan 1 (returns $0)
     ->  Limit  (cost=0.00..69.76 rows=1 width=8)
           ->  Index Scan using tmp_as on amplifier_saturation  (cost=0.00..861900.22 rows=12356 width=8)
                 Index Cond: ((lddate IS NOT NULL) AND (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))

Maybe someone smarter than me comes up with a better answer?

Unused index in range of dates query (Postgresql, timestamp with time zone)

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?

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)

mustang=# explain select count(*) from bss.amplifier_saturation where start >= '1987-12-31 00:00:00' and start <=  '1988-04-09 00:00:00';
                                                                      QUERY PLAN                                                                       
-------------------------------------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=38711.84..38711.85 rows=1 width=0)
   ->  Index Scan using amplifier_saturation_start on amplifier_saturation  (cost=0.00..38681.47 rows=12146 width=0)
         Index Cond: ((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))
(3 rows)

mustang=# create index tmp_as on bss.amplifier_saturation (lddate desc, start);
CREATE INDEX
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=69.76..69.77 rows=1 width=0)
   InitPlan 1 (returns $0)
     ->  Limit  (cost=0.00..69.76 rows=1 width=8)
           ->  Index Scan using tmp_as on amplifier_saturation  (cost=0.00..861900.22 rows=12356 width=8)
                 Index Cond: ((lddate IS NOT NULL) AND (start >= '1987-12-31 00:00:00-08'::timestamp with time zone) AND (start <= '1988-04-09 00:00:00-07'::timestamp with ti
me zone))
(5 rows)

Unused index in range of dates query (PostgreSQL, timestamp with time zone)

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?

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)

mustang=# explain select count(*) from bss.amplifier_saturation 
where start >= '1987-12-31 00:00:00' 
and   start <= '1988-04-09 00:00:00';
                                                                      QUERY PLAN                                                                       
-------------------------------------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=38711.84..38711.85 rows=1 width=0)
   ->  Index Scan using amplifier_saturation_start on amplifier_saturation  (cost=0.00..38681.47 rows=12146 width=0)
         Index Cond: ((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))
(3 rows)

mustang=# create index tmp_as on bss.amplifier_saturation (lddate desc, start);
CREATE INDEX
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=69.76..69.77 rows=1 width=0)
   InitPlan 1 (returns $0)
     ->  Limit  (cost=0.00..69.76 rows=1 width=8)
           ->  Index Scan using tmp_as on amplifier_saturation  (cost=0.00..861900.22 rows=12356 width=8)
                 Index Cond: ((lddate IS NOT NULL) AND (start >= '1987-12-31 00:00:00-08'::timestamp with time zone) AND (start <= '1988-04-09 00:00:00-07'::timestamp with ti
me zone))
(5 rows)

edit:

edit 1:

edit 2:

OK, adding an index on (lddate desc, start) works (not sure the desc is required). It can then use a pure index approach (IIUC) (and it runs much faster):

mustang=# create index tmp_as on bss.amplifier_saturation (lddate desc, start);
CREATE INDEX
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=69.76..69.77 rows=1 width=0)
   InitPlan 1 (returns $0)
     ->  Limit  (cost=0.00..69.76 rows=1 width=8)
           ->  Index Scan using tmp_as on amplifier_saturation  (cost=0.00..861900.22 rows=12356 width=8)
                 Index Cond: ((lddate IS NOT NULL) AND (start >= '1987-12-31 00:00:00-08'::timestamp with time zone) AND (start <= '1988-04-09 00:00:00-07'::timestamp with ti
me zone))
(5 rows)

So, I guess to answer my own question, it seems like the cost of accessing the data 3,500 times is slower than a 30,000,000 value scan (yay spinning disks). While a pure index scan is clearly better.

I'll post an answer myself in a while, but am waiting in case someone smarter than me comes along...