Bitmap Heap Scan taking ~60x as long for table when queried aspartition

Поиск
Список
Период
Сортировка
От Stephen Froehlich
Тема Bitmap Heap Scan taking ~60x as long for table when queried aspartition
Дата
Msg-id CY1PR0601MB19271A465ABE4D254AFB6C4EE5F70@CY1PR0601MB1927.namprd06.prod.outlook.com
обсуждение исходный текст
Ответы Re: Bitmap Heap Scan taking ~60x as long for table when queried aspartition
Список pgsql-novice

I have a fairly large partitioned table, but annoyingly if I query the hypertable directly it takes ~60x as long to scan each subtable than if I query each sub-table directly.  I have freshly vacuumed and analyzed the tables in question and this persists.  What is going on?

 

Thanks,

Stephen

 

EXPLAIN ANALYZE

SELECT

    SUM( server_tx_bytes + client_tx_bytes ) AS tot_bytes,

    date_trunc( 'minute', start_time )       AS start_min

FROM

    raptor_global_bitrate_20171101_cmts1

WHERE

    client_ip_md5='28903ff5-1bb6-2533-23e7-ac5218b30008'

AND start_time >= '2017-10-28 00:00:00 UTC'

AND start_time < '2017-11-28 00:00:00 UTC'

GROUP BY

    start_min;

 

HashAggregate  (cost=87530.06..87791.23 rows=17411 width=40) (actual time=2618.322..2619.004 rows=1440 loops=1)

  Group Key: date_trunc('minute'::text, start_time)

  ->  Bitmap Heap Scan on raptor_global_bitrate_20171101_cmts1  (cost=586.25..87302.95 rows=30281 width=24) (actual time=67.735..2548.289 rows=85807 loops=1)

        Recheck Cond: (client_ip_md5 = '28903ff5-1bb6-2533-23e7-ac5218b30008'::uuid)

        Filter: ((start_time >= '2017-10-28 00:00:00+00'::timestamp with time zone) AND (start_time < '2017-11-28 00:00:00+00'::timestamp with time zone))

        Heap Blocks: exact=85800

        ->  Bitmap Index Scan on raptor_global_bitrate_20171101_cmts1_client_ip_md5_idx  (cost=0.00..578.68 rows=30281 width=0) (actual time=47.199..47.199 rows=85807 loops=1)

              Index Cond: (client_ip_md5 = '28903ff5-1bb6-2533-23e7-ac5218b30008'::uuid)

Planning time: 1.768 ms

Execution time: 2619.368 ms

 

 

EXPLAIN ANALYZE

SELECT

    SUM( server_tx_bytes + client_tx_bytes ) AS tot_bytes,

    date_trunc( 'minute', start_time )       AS start_min

FROM

    raptor_global_bitrate_part

WHERE

    client_ip_md5='28903ff5-1bb6-2533-23e7-ac5218b30008'

AND start_time >= '2017-10-28 00:00:00 UTC'

AND start_time < '2017-11-28 00:00:00 UTC'

GROUP BY

    start_min;

                                ->  Parallel Bitmap Heap Scan on raptor_global_bitrate_20171101_cmts1  (cost=591.99..87814.50 rows=7659 width=24) (actual time=176.151..24760.668 rows=14301 loops=6)

                                      Recheck Cond: (client_ip_md5 = '28903ff5-1bb6-2533-23e7-ac5218b30008'::uuid)

                                      Filter: ((start_time >= '2017-10-28 00:00:00+00'::timestamp with time zone) AND (start_time < '2017-11-28 00:00:00+00'::timestamp with time zone))

                                      Heap Blocks: exact=14477

                                      ->  Bitmap Index Scan on raptor_global_bitrate_20171101_cmts1_client_ip_md5_idx  (cost=0.00..584.34 rows=30635 width=0) (actual time=131.289..131.289 rows=85807 loops=1)

                                            Index Cond: (client_ip_md5 = '28903ff5-1bb6-2533-23e7-ac5218b30008'::uuid)

Execution time for this sub-table: 148,432.719 ms

(Note this is 24760.668 * 6)

 

Stephen Froehlich
Sr. Strategist, CableLabs®


s.froehlich@cablelabs.com

Tel: +1 (303) 661-3708

 

В списке pgsql-novice по дате отправления:

Предыдущее
От: Tomer Praizler
Дата:
Сообщение: Re: When should I start and setup a slave replication?
Следующее
От: Laurenz Albe
Дата:
Сообщение: Re: Bitmap Heap Scan taking ~60x as long for table when queried aspartition