Skip to content

Instantly share code, notes, and snippets.

@masayuki038

masayuki038/cstore_parallel_scan.md

Last active August 19, 2018 04:00
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save masayuki038/daa63a21f8c16ffa8138b50db9129ced to your computer and use it in GitHub Desktop.
Save masayuki038/daa63a21f8c16ffa8138b50db9129ced to your computer and use it in GitHub Desktop.
Download ZIP
Raw
cstore_parallel_scan.md

Test schema/data

Tables(partitioned or not)

  • customer_reviews: FOREIGN TABLE(not-partitioned)
  • customer_reviews_cstore: FOREIGN TABLE(partitioned by product_group)

table_type

cstore_test=# select table_name, table_type from information_schema.tables where table_schema='public';
       table_name        | table_type
-------------------------+------------
 customer_reviews        | FOREIGN
 customer_reviews_cstore | BASE TABLE
 dvd                     | FOREIGN
 music                   | FOREIGN
 book                    | FOREIGN
 software                | FOREIGN
 ce                      | FOREIGN
 toy                     | FOREIGN
 video                   | FOREIGN
(9 rows)

Time: 0.762 ms

inherit

cstore_test=# SELECT
cstore_test-#     nmsp_parent.nspname AS parent_schema,
cstore_test-#     parent.relname      AS parent,
cstore_test-#     nmsp_child.nspname  AS child_schema,
cstore_test-#     child.relname       AS child
cstore_test-# FROM pg_inherits
cstore_test-#     JOIN pg_class parent            ON pg_inherits.inhparent = parent.oid
cstore_test-#     JOIN pg_class child             ON pg_inherits.inhrelid   = child.oid
cstore_test-#     JOIN pg_namespace nmsp_parent   ON nmsp_parent.oid  = parent.relnamespace
cstore_test-#     JOIN pg_namespace nmsp_child    ON nmsp_child.oid   = child.relnamespace
cstore_test-# WHERE parent.relname='customer_reviews';
 parent_schema | parent | child_schema | child
---------------+--------+--------------+-------
(0 rows)

Time: 0.815 ms
cstore_test=# SELECT
    nmsp_parent.nspname AS parent_schema,
    parent.relname      AS parent,
    nmsp_child.nspname  AS child_schema,
    child.relname       AS child
FROM pg_inherits
    JOIN pg_class parent            ON pg_inherits.inhparent = parent.oid
    JOIN pg_class child             ON pg_inherits.inhrelid   = child.oid
    JOIN pg_namespace nmsp_parent   ON nmsp_parent.oid  = parent.relnamespace
    JOIN pg_namespace nmsp_child    ON nmsp_child.oid   = child.relnamespace
WHERE parent.relname='customer_reviews_cstore';
 parent_schema |         parent          | child_schema |  child
---------------+-------------------------+--------------+----------
 public        | customer_reviews_cstore | public       | dvd
 public        | customer_reviews_cstore | public       | music
 public        | customer_reviews_cstore | public       | book
 public        | customer_reviews_cstore | public       | software
 public        | customer_reviews_cstore | public       | ce
 public        | customer_reviews_cstore | public       | toy
 public        | customer_reviews_cstore | public       | video
(7 rows)

Time: 0.764 ms

schema

cstore_test=# \d customer_reviews;
                        Foreign table "public.customer_reviews"
        Column        |      Type       | Collation | Nullable | Default | FDW options
----------------------+-----------------+-----------+----------+---------+-------------
 customer_id          | text            |           |          |         |
 review_date          | date            |           |          |         |
 review_rating        | integer         |           |          |         |
 review_votes         | integer         |           |          |         |
 review_helpful_votes | integer         |           |          |         |
 product_id           | character(10)   |           |          |         |
 product_title        | text            |           |          |         |
 product_sales_rank   | bigint          |           |          |         |
 product_group        | text            |           |          |         |
 product_category     | text            |           |          |         |
 product_subcategory  | text            |           |          |         |
 similar_product_ids  | character(10)[] |           |          |         |
Server: cstore_server
FDW options: (compression 'pglz')

cstore_test=# \d customer_reviews_cstore;
                 Table "public.customer_reviews_cstore"
        Column        |      Type       | Collation | Nullable | Default
----------------------+-----------------+-----------+----------+---------
 customer_id          | text            |           |          |
 review_date          | date            |           |          |
 review_rating        | integer         |           |          |
 review_votes         | integer         |           |          |
 review_helpful_votes | integer         |           |          |
 product_id           | character(10)   |           |          |
 product_title        | text            |           |          |
 product_sales_rank   | bigint          |           |          |
 product_group        | text            |           |          |
 product_category     | text            |           |          |
 product_subcategory  | text            |           |          |
 similar_product_ids  | character(10)[] |           |          |
Partition key: LIST (product_group)
Number of partitions: 7 (Use \d+ to list them.)

Group by partition key

Query result

cstore_test=# select product_group, count(*) from customer_reviews group by product_group;
 product_group |  count
---------------+---------
 DVD           |  121418
 Video         |  142235
 Music         |  300628
 Book          | 1198218
 Software      |       1
 CE            |       1
 Toy           |       3
(7 rows)

Time: 258.150 ms
cstore_test=# select product_group, count(*) from customer_reviews_cstore group by product_group;
 product_group |  count
---------------+---------
 Book          | 1198218
 CE            |       1
 DVD           |  121418
 Music         |  300628
 Software      |       1
 Toy           |       3
 Video         |  142235
(7 rows)

Time: 264.642 ms

Execution plan

cstore_test=# explain analyse verbose select product_group, count(*) from customer_reviews group by product_group;
                                                                                                      QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 HashAggregate  (cost=27512.98..27513.02 rows=4 width=13) (actual time=1756.163..1756.170 rows=7 loops=1)
   Output: product_group, count(*)
   Group Key: customer_reviews.product_group
   ->  Foreign Scan on public.customer_reviews  (cost=0.00..18700.46 rows=1762504 width=5) (actual time=0.408..824.077 rows=1762504 loops=1)
         Output: customer_id, review_date, review_rating, review_votes, review_helpful_votes, product_id, product_title, product_sales_rank, product_group, product_category, product_subcategory, similar_product_ids
         CStore File Size: 105717760
 Planning Time: 0.103 ms
 Execution Time: 1756.261 ms
(8 rows)

Time: 1756.741 ms (00:01.757)
cstore_test=# explain analyse verbose select product_group, count(*) from customer_reviews_cstore group by product_group;
                                                                        QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------
 Finalize GroupAggregate  (cost=21029.87..21080.54 rows=200 width=40) (actual time=2485.120..2485.142 rows=7 loops=1)
   Output: book.product_group, count(*)
   Group Key: book.product_group
   ->  Gather Merge  (cost=21029.87..21076.54 rows=400 width=40) (actual time=2485.111..2485.122 rows=7 loops=1)
         Output: book.product_group, (PARTIAL count(*))
         Workers Planned: 2
         Workers Launched: 2
         ->  Sort  (cost=20029.84..20030.34 rows=200 width=40) (actual time=1296.846..1296.848 rows=2 loops=3)
               Output: book.product_group, (PARTIAL count(*))
               Sort Key: book.product_group
               Sort Method: quicksort  Memory: 25kB
               Worker 0:  Sort Method: quicksort  Memory: 25kB
               Worker 1:  Sort Method: quicksort  Memory: 25kB
               Worker 0: actual time=744.301..744.302 rows=1 loops=1
               Worker 1: actual time=2456.731..2456.731 rows=1 loops=1
               ->  Partial HashAggregate  (cost=20020.20..20022.20 rows=200 width=40) (actual time=1296.789..1296.791 rows=2 loops=3)
                     Output: book.product_group, PARTIAL count(*)
                     Group Key: book.product_group
                     Worker 0: actual time=744.238..744.239 rows=1 loops=1
                     Worker 1: actual time=2456.640..2456.641 rows=1 loops=1
                     ->  Parallel Append  (cost=0.00..16348.32 rows=734377 width=32) (actual time=0.548..920.470 rows=587501 loops=3)
                           Worker 0: actual time=0.606..526.072 rows=300628 loops=1
                           Worker 1: actual time=0.954..1748.065 rows=1198218 loops=1
                           ->  Foreign Scan on public.book  (cost=0.00..12676.43 rows=1198218 width=32) (actual time=0.950..637.320 rows=1198218 loops=1)
                                 Output: book.product_group
                                 CStore File Size: 68247552
                                 Worker 1: actual time=0.950..637.320 rows=1198218 loops=1
                           ->  Foreign Scan on public.music  (cost=0.00..3172.28 rows=300628 width=32) (actual time=0.603..192.336 rows=300628 loops=1)
                                 Output: music.product_group
                                 CStore File Size: 16318464
                                 Worker 0: actual time=0.603..192.336 rows=300628 loops=1
                           ->  Foreign Scan on public.video  (cost=0.00..1502.77 rows=142235 width=32) (actual time=0.390..98.030 rows=142235 loops=1)
                                 Output: video.product_group
                                 CStore File Size: 7905280
                           ->  Foreign Scan on public.dvd  (cost=0.00..1281.18 rows=121418 width=32) (actual time=0.188..80.072 rows=121418 loops=1)
                                 Output: dvd.product_group
                                 CStore File Size: 6586368
                           ->  Foreign Scan on public.toy  (cost=0.00..0.36 rows=3 width=32) (actual time=0.045..0.048 rows=3 loops=1)
                                 Output: toy.product_group
                                 CStore File Size: 32768
                           ->  Foreign Scan on public.ce  (cost=0.00..0.34 rows=1 width=32) (actual time=0.046..0.047 rows=1 loops=1)
                                 Output: ce.product_group
                                 CStore File Size: 32768
                           ->  Foreign Scan on public.software  (cost=0.00..0.34 rows=1 width=32) (actual time=0.084..0.085 rows=1 loops=1)
                                 Output: software.product_group
                                 CStore File Size: 32768
 Planning Time: 1.018 ms
 Execution Time: 2486.953 ms
(48 rows)

Time: 2488.421 ms (00:02.488)

Group by non partition key

Query result

cstore_test=# select product_sales_rank, cnt from (select product_sales_rank, count(*) as cnt from customer_reviews group by product_sales_rank) m order by product_sales_rank limit 10;
 product_sales_rank | cnt
--------------------+-----
                 -1 |  35
                  0 |   9
                  1 |  26
                  2 |  15
                  7 |  38
                  8 |   5
                 12 |  22
                 14 |   3
                 16 |  14
                 17 |  75
(10 rows)

Time: 424.297 ms
cstore_test=# select product_sales_rank, cnt from (select product_sales_rank, count(*) as cnt from customer_reviews_cstore group by product_sales_rank) m order by product_sales_rank limit 10;
 product_sales_rank | cnt
--------------------+-----
                 -1 |  35
                  0 |   9
                  1 |  26
                  2 |  15
                  7 |  38
                  8 |   5
                 12 |  22
                 14 |   3
                 16 |  14
                 17 |  75
(10 rows)

Query results(product_sales_rank=-1 by each partition)

cstore_test=# select 'book', count(*) from book where product_sales_rank=-1
cstore_test-# union all
cstore_test-# select 'music', count(*) from music where product_sales_rank=-1
cstore_test-# union all
cstore_test-# select 'video', count(*) from video where product_sales_rank=-1
cstore_test-# union all
cstore_test-# select 'toy', count(*) from toy where product_sales_rank=-1
cstore_test-# union all
cstore_test-# select 'ce', count(*) from ce where product_sales_rank=-1
cstore_test-# union all
cstore_test-# select 'software', count(*) from software where product_sales_rank=-1
cstore_test-# union all
cstore_test-# select 'dvd', count(*) from dvd where product_sales_rank=-1;
 ?column? | count
----------+-------
 software |     0
 ce       |     0
 toy      |     0
 dvd      |     0
 video    |     0
 music    |     6
 book     |    29
(7 rows)

Time: 109.880 ms
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment