pg11: Query using index, but only for half the partitions

[Stefan Knecht <knecht.stefan@xxxxxxxxx>]

Hello all

Does anyone have an explanation for this?

Query uses only three columns of the table "snap" and all three are in an index.

The planner seems to think that some partitions are better scanned in full. Yet for the other half of them it's using the index just fine.

Can someone enlighten me what could be happening here?

Cheers

Stefan

Table:

\d+ snap
                                                              Table "statspack.snap"
      Column      |            Type             | Collation | Nullable |             Default              | Storage  | Stats target | Description
------------------+-----------------------------+-----------+----------+----------------------------------+----------+--------------+-------------
 snap_id          | integer                     |           |          | nextval('snap_id_seq'::regclass) | plain    |              |
 start_time       | timestamp without time zone |           |          |                                  | plain    |              |
 end_time         | timestamp without time zone |           |          |                                  | plain    |              |
 metadata_snap_id | integer                     |           |          |                                  | plain    |              |
 instance_id      | character varying(200)      |           |          |                                  | extended |              |
 data_size        | bigint                      |           |          |                                  | plain    |              |
 state            | text                        |           |          |                                  | extended |              |
Partition key: RANGE (start_time)
Indexes:
    "snap_snap_id_ix" UNIQUE, btree (start_time, snap_id)
    "snap_instance_id_ix" btree (start_time, snap_id, instance_id)
Partitions: snap_20200225 FOR VALUES FROM ('2020-02-25 00:00:00') TO ('2020-02-26 00:00:00'),
            snap_20200226 FOR VALUES FROM ('2020-02-26 00:00:00') TO ('2020-02-27 00:00:00'),

            ... (omitted for clarity)

            snap_20200423 FOR VALUES FROM ('2020-04-23 00:00:00') TO ('2020-04-24 00:00:00')

  

Query predicates:

    from statspack.cloudwatch_metrics m, statspack.snap s, statspack.settings l
   where m.snap_id = s.snap_id
     and m.start_time = s.start_time
     and s.snap_id = l.snap_id
     and s.start_time = l.start_time
     and l.name = 'max_connections'
     and s.instance_id::varchar = 'test01'
     and m.start_time > now() - interval '30 days'
   order by m.snap_id

Plan:

                            QUERY PLAN                            
-------------------------------------------------------------------------------------------------------------------------------------------------------
 WindowAgg  (cost=4972.00..4974.41 rows=21 width=212) (actual time=131.541..132.934 rows=2763 loops=1)
   ->  Subquery Scan on m  (cost=4972.00..4972.26 rows=21 width=124) (actual time=125.612..127.744 rows=2763 loops=1)
   ->  Sort  (cost=4972.00..4972.05 rows=21 width=188) (actual time=125.609..126.226 rows=2763 loops=1)
      Sort Key: m_1.snap_id
      Sort Method: quicksort  Memory: 830kB
      ->  WindowAgg  (cost=4970.49..4971.54 rows=21 width=188) (actual time=120.667..124.384 rows=2763 loops=1)
      ->  Sort  (cost=4970.49..4970.54 rows=21 width=147) (actual time=120.614..121.186 rows=2763 loops=1)
         Sort Key: (date_trunc('day'::text, m_1.start_time))
         Sort Method: quicksort  Memory: 485kB
         ->  WindowAgg  (cost=2050.81..4970.02 rows=21 width=147) (actual time=118.370..119.501 rows=2763 loops=1)
         ->  Nested Loop  (cost=2050.81..4968.92 rows=21 width=107) (actual time=7.974..113.780 rows=2763 loops=1)
            ->  Hash Join  (cost=2050.53..4309.54 rows=18 width=40) (actual time=7.942..83.302 rows=2763 loops=1)
            Hash Cond: ((m_1.snap_id = s.snap_id) AND (m_1.start_time = s.start_time))
            ->  Append  (cost=0.00..1870.00 rows=51845 width=28) (actual time=0.059..60.010 rows=51681 loops=1)
               Subplans Removed: 19
               ->  Seq Scan on cloudwatch_metrics_20200324 m_1  (cost=0.00..37.53 rows=1068 width=28) (actual time=0.058..0.676 rows=1070 loops=1)
               Filter: (start_time > (now() - '30 days'::interval))
               Rows Removed by Filter: 160
               ->  Seq Scan on cloudwatch_metrics_20200325 m_2  (cost=0.00..54.59 rows=1805 width=28) (actual time=0.006..1.062 rows=1805 loops=1)
               Filter: (start_time > (now() - '30 days'::interval))

               ... ( omitted for clarity )

               ->  Seq Scan on cloudwatch_metrics_20200423 m_31  (cost=0.00..17.99 rows=571 width=28) (actual time=0.008..0.325 rows=572 loops=1)
               Filter: (start_time > (now() - '30 days'::interval))
            ->  Hash  (cost=2007.12..2007.12 rows=2894 width=12) (actual time=7.836..7.836 rows=2878 loops=1)
               Buckets: 4096  Batches: 1  Memory Usage: 167kB
               ->  Append  (cost=0.00..2007.12 rows=2894 width=12) (actual time=0.018..6.841 rows=2878 loops=1)
               ->  Seq Scan on snap_20200225 s  (cost=0.00..1.19 rows=1 width=12) (actual time=0.006..0.007 rows=0 loops=1)
                  Filter: ((instance_id)::text = 'test01'::text)
                  Rows Removed by Filter: 15
               ->  Seq Scan on snap_20200226 s_1  (cost=0.00..1.01 rows=1 width=12) (actual time=0.003..0.003 rows=0 loops=1)
                  Filter: ((instance_id)::text = 'test01'::text)
                  Rows Removed by Filter: 1
               ->  Seq Scan on snap_20200228 s_2  (cost=0.00..1.02 rows=1 width=12) (actual time=0.004..0.004 rows=0 loops=1)
                  Filter: ((instance_id)::text = 'test01'::text)
                  Rows Removed by Filter: 2
               ->  Seq Scan on snap_20200303 s_3  (cost=0.00..1.05 rows=2 width=12) (actual time=0.003..0.005 rows=2 loops=1)
                  Filter: ((instance_id)::text = 'test01'::text)
                  Rows Removed by Filter: 2
               ->  Seq Scan on snap_20200304 s_4  (cost=0.00..1.07 rows=6 width=12) (actual time=0.003..0.005 rows=6 loops=1)
                  Filter: ((instance_id)::text = 'test01'::text)
               ->  Seq Scan on snap_20200305 s_5  (cost=0.00..1.12 rows=2 width=12) (actual time=0.003..0.004 rows=2 loops=1)
                  Filter: ((instance_id)::text = 'test01'::text)
                  Rows Removed by Filter: 8
               ->  Seq Scan on snap_20200306 s_6  (cost=0.00..1.04 rows=1 width=12) (actual time=0.003..0.003 rows=0 loops=1)
                  Filter: ((instance_id)::text = 'test01'::text)
                  Rows Removed by Filter: 3
               ->  Seq Scan on snap_20200309 s_7  (cost=0.00..1.02 rows=1 width=12) (actual time=0.278..0.279 rows=0 loops=1)
                  Filter: ((instance_id)::text = 'test01'::text)
                  Rows Removed by Filter: 2
               ->  Seq Scan on snap_20200310 s_8  (cost=0.00..1.07 rows=1 width=12) (actual time=0.005..0.005 rows=0 loops=1)
                  Filter: ((instance_id)::text = 'test01'::text)
                  Rows Removed by Filter: 6
               ->  Seq Scan on snap_20200311 s_9  (cost=0.00..1.07 rows=1 width=12) (actual time=0.003..0.003 rows=0 loops=1)
                  Filter: ((instance_id)::text = 'test01'::text)
                  Rows Removed by Filter: 6
               ->  Seq Scan on snap_20200313 s_10  (cost=0.00..1.02 rows=1 width=12) (actual time=0.003..0.003 rows=0 loops=1)
                  Filter: ((instance_id)::text = 'test01'::text)
                  Rows Removed by Filter: 2
               ->  Seq Scan on snap_20200316 s_11  (cost=0.00..1.95 rows=1 width=12) (actual time=0.009..0.009 rows=0 loops=1)
                  Filter: ((instance_id)::text = 'test01'::text)
                  Rows Removed by Filter: 76
               ->  Seq Scan on snap_20200317 s_12  (cost=0.00..2.23 rows=1 width=12) (actual time=0.012..0.012 rows=0 loops=1)
                  Filter: ((instance_id)::text = 'test01'::text)
                  Rows Removed by Filter: 98
               ->  Seq Scan on snap_20200318 s_13  (cost=0.00..2.20 rows=1 width=12) (actual time=0.011..0.011 rows=0 loops=1)
                  Filter: ((instance_id)::text = 'test01'::text)
                  Rows Removed by Filter: 96
               ->  Seq Scan on snap_20200319 s_14  (cost=0.00..2.21 rows=1 width=12) (actual time=0.011..0.011 rows=0 loops=1)
                  Filter: ((instance_id)::text = 'test01'::text)
                  Rows Removed by Filter: 97
               ->  Seq Scan on snap_20200320 s_15  (cost=0.00..2.20 rows=1 width=12) (actual time=0.012..0.012 rows=0 loops=1)
                  Filter: ((instance_id)::text = 'test01'::text)
                  Rows Removed by Filter: 96
               ->  Seq Scan on snap_20200321 s_16  (cost=0.00..2.20 rows=1 width=12) (actual time=0.011..0.011 rows=0 loops=1)
                  Filter: ((instance_id)::text = 'test01'::text)
                  Rows Removed by Filter: 96
               ->  Seq Scan on snap_20200322 s_17  (cost=0.00..2.20 rows=1 width=12) (actual time=0.011..0.011 rows=0 loops=1)
                  Filter: ((instance_id)::text = 'test01'::text)
                  Rows Removed by Filter: 96
               ->  Seq Scan on snap_20200323 s_18  (cost=0.00..2.20 rows=1 width=12) (actual time=0.011..0.011 rows=0 loops=1)
                  Filter: ((instance_id)::text = 'test01'::text)
                  Rows Removed by Filter: 96
               ->  Index Only Scan using snap_20200324_start_time_snap_id_instance_id_idx on snap_20200324 s_19  (cost=0.28..53.96 rows=59 width=12) (actual time=0.014..0.074 rows=59 loops=1)
                  Index Cond: (instance_id = 'test01'::text)
                  Heap Fetches: 0
               ->  Index Only Scan using snap_20200325_start_time_snap_id_instance_id_idx on snap_20200325 s_20  (cost=0.28..74.80 rows=95 width=12) (actual time=0.011..0.113 rows=95 loops=1)
                  Index Cond: (instance_id = 'test01'::text)
                  Heap Fetches: 0
               ->  Seq Scan on snap_20200326 s_21  (cost=0.00..69.66 rows=95 width=12) (actual time=0.007..0.276 rows=95 loops=1)
                  Filter: ((instance_id)::text = 'test01'::text)
                  Rows Removed by Filter: 1718
               ->  Seq Scan on snap_20200327 s_22  (cost=0.00..73.65 rows=95 width=12) (actual time=0.007..0.255 rows=95 loops=1)
                  Filter: ((instance_id)::text = 'test01'::text)
                  Rows Removed by Filter: 1717
               ->  Seq Scan on snap_20200328 s_23  (cost=0.00..51.73 rows=95 width=12) (actual time=0.007..0.225 rows=95 loops=1)
                  Filter: ((instance_id)::text = 'test01'::text)
                  Rows Removed by Filter: 1723
               ->  Seq Scan on snap_20200329 s_24  (cost=0.00..51.73 rows=95 width=12) (actual time=0.007..0.221 rows=95 loops=1)
                  Filter: ((instance_id)::text = 'test01'::text)
                  Rows Removed by Filter: 1723
               ->  Seq Scan on snap_20200330 s_25  (cost=0.00..54.65 rows=95 width=12) (actual time=0.006..0.223 rows=95 loops=1)
                  Filter: ((instance_id)::text = 'test01'::text)
                  Rows Removed by Filter: 1717
               ->  Seq Scan on snap_20200331 s_26  (cost=0.00..60.80 rows=96 width=12) (actual time=0.006..0.228 rows=96 loops=1)
                  Filter: ((instance_id)::text = 'test01'::text)
                  Rows Removed by Filter: 1728
               ->  Index Only Scan using snap_20200401_start_time_snap_id_instance_id_idx on snap_20200401 s_27  (cost=0.28..74.78 rows=95 width=12) (actual time=0.012..0.107 rows=95 loops=1)
                  Index Cond: (instance_id = 'test01'::text)
                  Heap Fetches: 0
               ->  Index Only Scan using snap_20200402_start_time_snap_id_instance_id_idx on snap_20200402 s_28  (cost=0.28..74.92 rows=96 width=12) (actual time=0.009..0.101 rows=96 loops=1)
                  Index Cond: (instance_id = 'test01'::text)
                  Heap Fetches: 0
               ->  Index Only Scan using snap_20200403_start_time_snap_id_instance_id_idx on snap_20200403 s_29  (cost=0.28..74.92 rows=96 width=12) (actual time=0.009..0.099 rows=96 loops=1)
                  Index Cond: (instance_id = 'test01'::text)
                  Heap Fetches: 0
               ->  Index Only Scan using snap_20200404_start_time_snap_id_instance_id_idx on snap_20200404 s_30  (cost=0.28..74.92 rows=96 width=12) (actual time=0.010..0.102 rows=96 loops=1)
                  Index Cond: (instance_id = 'test01'::text)
                  Heap Fetches: 0
               ->  Index Only Scan using snap_20200405_start_time_snap_id_instance_id_idx on snap_20200405 s_31  (cost=0.28..74.92 rows=96 width=12) (actual time=0.011..0.104 rows=96 loops=1)
                  Index Cond: (instance_id = 'test01'::text)
                  Heap Fetches: 0
               ->  Index Only Scan using snap_20200406_start_time_snap_id_instance_id_idx on snap_20200406 s_32  (cost=0.28..74.92 rows=96 width=12) (actual time=0.010..0.102 rows=96 loops=1)
                  Index Cond: (instance_id = 'test01'::text)
                  Heap Fetches: 0
               ->  Index Only Scan using snap_20200407_start_time_snap_id_instance_id_idx on snap_20200407 s_33  (cost=0.28..74.92 rows=96 width=12) (actual time=0.011..0.139 rows=96 loops=1)
                  Index Cond: (instance_id = 'test01'::text)
                  Heap Fetches: 0
               ->  Index Only Scan using snap_20200408_start_time_snap_id_instance_id_idx on snap_20200408 s_34  (cost=0.28..74.92 rows=96 width=12) (actual time=0.011..0.115 rows=96 loops=1)
                  Index Cond: (instance_id = 'test01'::text)
                  Heap Fetches: 0
               ->  Index Only Scan using snap_20200409_start_time_snap_id_instance_id_idx on snap_20200409 s_35  (cost=0.28..74.92 rows=96 width=12) (actual time=0.012..0.104 rows=96 loops=1)
                  Index Cond: (instance_id = 'test01'::text)
                  Heap Fetches: 0
               ->  Seq Scan on snap_20200410 s_36  (cost=0.00..50.80 rows=96 width=12) (actual time=0.009..0.225 rows=96 loops=1)
                  Filter: ((instance_id)::text = 'test01'::text)
                  Rows Removed by Filter: 1728
               ->  Index Only Scan using snap_20200411_start_time_snap_id_instance_id_idx on snap_20200411 s_37  (cost=0.28..74.92 rows=96 width=12) (actual time=0.011..0.106 rows=96 loops=1)
                  Index Cond: (instance_id = 'test01'::text)
                  Heap Fetches: 0
               ->  Index Only Scan using snap_20200412_start_time_snap_id_instance_id_idx on snap_20200412 s_38  (cost=0.28..74.92 rows=96 width=12) (actual time=0.036..0.129 rows=96 loops=1)
                  Index Cond: (instance_id = 'test01'::text)
                  Heap Fetches: 0
               ->  Seq Scan on snap_20200413 s_39  (cost=0.00..56.44 rows=96 width=12) (actual time=0.008..0.246 rows=96 loops=1)
                  Filter: ((instance_id)::text = 'test01'::text)
                  Rows Removed by Filter: 1699
               ->  Seq Scan on snap_20200414 s_40  (cost=0.00..46.60 rows=96 width=12) (actual time=0.007..0.237 rows=96 loops=1)
                  Filter: ((instance_id)::text = 'test01'::text)
                  Rows Removed by Filter: 1632
               ->  Seq Scan on snap_20200415 s_41  (cost=0.00..45.60 rows=96 width=12) (actual time=0.007..0.233 rows=96 loops=1)
                  Filter: ((instance_id)::text = 'test01'::text)
                  Rows Removed by Filter: 1632
               ->  Seq Scan on snap_20200416 s_42  (cost=0.00..52.60 rows=96 width=12) (actual time=0.007..0.256 rows=96 loops=1)
                  Filter: ((instance_id)::text = 'test01'::text)
                  Rows Removed by Filter: 1632
               ->  Index Only Scan using snap_20200417_start_time_snap_id_instance_id_idx on snap_20200417 s_43  (cost=0.28..70.20 rows=96 width=12) (actual time=0.013..0.099 rows=96 loops=1)
                  Index Cond: (instance_id = 'test01'::text)
                  Heap Fetches: 0
               ->  Seq Scan on snap_20200418 s_44  (cost=0.00..59.60 rows=96 width=12) (actual time=0.006..0.231 rows=96 loops=1)
                  Filter: ((instance_id)::text = 'test01'::text)
                  Rows Removed by Filter: 1632
               ->  Seq Scan on snap_20200419 s_45  (cost=0.00..47.60 rows=96 width=12) (actual time=0.006..0.233 rows=96 loops=1)
                  Filter: ((instance_id)::text = 'test01'::text)
                  Rows Removed by Filter: 1632
               ->  Index Only Scan using snap_20200420_start_time_snap_id_instance_id_idx on snap_20200420 s_46  (cost=0.28..70.20 rows=96 width=12) (actual time=0.012..0.097 rows=96 loops=1)
                  Index Cond: (instance_id = 'test01'::text)
                  Heap Fetches: 0
               ->  Seq Scan on snap_20200421 s_47  (cost=0.00..53.60 rows=96 width=12) (actual time=0.006..0.249 rows=96 loops=1)
                  Filter: ((instance_id)::text = 'test01'::text)
                  Rows Removed by Filter: 1632
               ->  Index Only Scan using snap_20200422_start_time_snap_id_instance_id_idx on snap_20200422 s_48  (cost=0.28..70.20 rows=96 width=12) (actual time=0.012..0.100 rows=96 loops=1)
                  Index Cond: (instance_id = 'test01'::text)
                  Heap Fetches: 0
               ->  Seq Scan on snap_20200423 s_49  (cost=0.00..25.20 rows=32 width=12) (actual time=0.007..0.106 rows=32 loops=1)
                  Filter: ((instance_id)::text = 'test01'::text)
                  Rows Removed by Filter: 544
            ->  Append  (cost=0.28..36.13 rows=50 width=19) (actual time=0.007..0.008 rows=1 loops=2763)
            ->  Index Only Scan using settings_20200225_start_time_snap_id_name_value_idx on settings_20200225 l  (cost=0.28..0.31 rows=1 width=20) (never executed)
               Index Cond: ((start_time = m_1.start_time) AND (snap_id = m_1.snap_id) AND (name = 'max_connections'::text))
               Heap Fetches: 0

               ... ( omitted for clarity )

            ->  Index Only Scan using settings_20200423_start_time_snap_id_name_value_idx on settings_20200423 l_49  (cost=0.42..0.66 rows=1 width=19) (actual time=0.006..0.007 rows=1 loops=31)
               Index Cond: ((start_time = m_1.start_time) AND (snap_id = m_1.snap_id) AND (name = 'max_connections'::text))
               Heap Fetches: 31
 Planning Time: 26.397 ms
 Execution Time: 134.803 ms
(383 rows)