Tweeted twitter.com/#!/StackDBAs/status/586810234586456064
8 shorten title, trim noise, clean up, add tags
source | link

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 theThe 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 Why does this not use the index on (start,amplifier_saturation_start? It seems to me like the DB should scan that to find the start descdate, and then continue through to delimit all entries 'til the end date, and finally sort that (small subset of) in desperation but it didn't helpdata for the maximum lddate (something like pp40-41 of SQL Performance Explained).

edit 1: 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))
(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?

  • 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 edit 2: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?

OK, addingAdding an index on (lddate desc, start) works (not sure the descdesc is required). It can then use a pure index approach (IIUC) (andand 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
metime zone))
(5 rows)

I'll post an answer myself in a while, but am waiting in caseMaybe someone smarter than me comes along...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?

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?

7 fix name, minor edits
source | link

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

I think maybe I am making a dumb mistake here, so apologies in advance. ButBut I have a query that is not using existing indices and I do not understand why. AmAm I reading the plan wrong? AreAre 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)

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)
6 added 1483 characters in body
source | link

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

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

5 added 277 characters in body
source | link
4 added 28 characters in body
source | link
3 added 886 characters in body
source | link
2 edited title
| link
1
source | link