public inbox for [email protected]help / color / mirror / Atom feed
[PATCH 1/3] bootstrap: convert Typ to a List* 8+ messages / 5 participants [nested] [flat]
* [PATCH 1/3] bootstrap: convert Typ to a List* @ 2020-11-20 02:48 Justin Pryzby <[email protected]> 0 siblings, 0 replies; 8+ messages in thread From: Justin Pryzby @ 2020-11-20 02:48 UTC (permalink / raw) --- src/backend/bootstrap/bootstrap.c | 69 ++++++++++++++----------------- 1 file changed, 31 insertions(+), 38 deletions(-) diff --git a/src/backend/bootstrap/bootstrap.c b/src/backend/bootstrap/bootstrap.c index 6f615e6622..18eb62ca47 100644 --- a/src/backend/bootstrap/bootstrap.c +++ b/src/backend/bootstrap/bootstrap.c @@ -159,7 +159,7 @@ struct typmap FormData_pg_type am_typ; }; -static struct typmap **Typ = NULL; +static List *Typ = NIL; /* List of struct typmap* */ static struct typmap *Ap = NULL; static Datum values[MAXATTR]; /* current row's attribute values */ @@ -597,7 +597,7 @@ boot_openrel(char *relname) * pg_type must be filled before any OPEN command is executed, hence we * can now populate the Typ array if we haven't yet. */ - if (Typ == NULL) + if (Typ == NIL) populate_typ_array(); if (boot_reldesc != NULL) @@ -688,7 +688,7 @@ DefineAttr(char *name, char *type, int attnum, int nullness) typeoid = gettype(type); - if (Typ != NULL) + if (Typ != NIL) { attrtypes[attnum]->atttypid = Ap->am_oid; attrtypes[attnum]->attlen = Ap->am_typ.typlen; @@ -877,36 +877,25 @@ populate_typ_array(void) Relation rel; TableScanDesc scan; HeapTuple tup; - int nalloc; - int i; - - Assert(Typ == NULL); - nalloc = 512; - Typ = (struct typmap **) - MemoryContextAlloc(TopMemoryContext, nalloc * sizeof(struct typmap *)); + Assert(Typ == NIL); rel = table_open(TypeRelationId, NoLock); scan = table_beginscan_catalog(rel, 0, NULL); - i = 0; while ((tup = heap_getnext(scan, ForwardScanDirection)) != NULL) { Form_pg_type typForm = (Form_pg_type) GETSTRUCT(tup); + struct typmap *newtyp; + MemoryContext old; - /* make sure there will be room for a trailing NULL pointer */ - if (i >= nalloc - 1) - { - nalloc *= 2; - Typ = (struct typmap **) - repalloc(Typ, nalloc * sizeof(struct typmap *)); - } - Typ[i] = (struct typmap *) - MemoryContextAlloc(TopMemoryContext, sizeof(struct typmap)); - Typ[i]->am_oid = typForm->oid; - memcpy(&(Typ[i]->am_typ), typForm, sizeof(Typ[i]->am_typ)); - i++; + old = MemoryContextSwitchTo(TopMemoryContext); + newtyp = (struct typmap *) palloc(sizeof(struct typmap)); + Typ = lappend(Typ, newtyp); + MemoryContextSwitchTo(old); + + newtyp->am_oid = typForm->oid; + memcpy(&newtyp->am_typ, typForm, sizeof(newtyp->am_typ)); } - Typ[i] = NULL; /* Fill trailing NULL pointer */ table_endscan(scan); table_close(rel, NoLock); } @@ -925,16 +914,17 @@ populate_typ_array(void) static Oid gettype(char *type) { - if (Typ != NULL) + if (Typ != NIL) { - struct typmap **app; + ListCell *lc; - for (app = Typ; *app != NULL; app++) + foreach (lc, Typ) { - if (strncmp(NameStr((*app)->am_typ.typname), type, NAMEDATALEN) == 0) + struct typmap *app = lfirst(lc); + if (strncmp(NameStr(app->am_typ.typname), type, NAMEDATALEN) == 0) { - Ap = *app; - return (*app)->am_oid; + Ap = app; + return app->am_oid; } } } @@ -980,14 +970,17 @@ boot_get_type_io_data(Oid typid, if (Typ != NULL) { /* We have the boot-time contents of pg_type, so use it */ - struct typmap **app; - struct typmap *ap; - - app = Typ; - while (*app && (*app)->am_oid != typid) - ++app; - ap = *app; - if (ap == NULL) + struct typmap *ap = NULL; + ListCell *lc; + + foreach (lc, Typ) + { + ap = lfirst(lc); + if (ap->am_oid == typid) + break; + } + + if (!ap || ap->am_oid != typid) elog(ERROR, "type OID %u not found in Typ list", typid); *typlen = ap->am_typ.typlen; -- 2.26.2 --------------9399ACFA5093E21681352885 Content-Type: text/x-patch; charset=UTF-8; name="0002-Allow-composite-types-in-bootstrap-20210117.patch" Content-Transfer-Encoding: 7bit Content-Disposition: attachment; filename="0002-Allow-composite-types-in-bootstrap-20210117.patch" ^ permalink raw reply [nested|flat] 8+ messages in thread
* benchmark results comparing versions 15.2 and 16 @ 2023-05-05 17:45 MARK CALLAGHAN <[email protected]> 0 siblings, 2 replies; 8+ messages in thread From: MARK CALLAGHAN @ 2023-05-05 17:45 UTC (permalink / raw) To: [email protected] Let me know if results like this shouldn't be posted here. This is mostly a hobby project for me - my other hobby is removing invasive weeds. I am happy to answer questions and run more tests, but turnaround for answers won't be instant. Getting results from Linux perf for these tests is on my TODO list. For now I am just re-running a subset of these to get more certainty that the regressions are real and not noise. These are results for the insert benchmark on a small server comparing performance for versions 15.2 and 16. For version 16 I built from source at git sha 1ab763fc. https://github.com/postgres/postgres/commit/1ab763fc22adc88e5d779817e7b42b25a9dd7c9e Late in the version 15 beta release cycle this benchmark found a significant regression. I don't see anything significant this time, but there are potential small regressions. More detail on how I run the benchmark and the HW is here, the server is small -- Beelink with 8 cores, 16G RAM and 1T NVMe SSD. http://smalldatum.blogspot.com/2023/05/the-insert-benchmark-postgres-versions.html Performance reports are linked below. But first, disclaimers: * the goal is to determine whether there are CPU improvements or regressions. To make that easier to spot I disable fsync on commit. * my scripts compute CPU/operation where operation is a SQL statement. However, CPU in this case is measured by vmstat and includes CPU from the benchmark client and Postgres server * the regressions here are small, usually less than 5% which means it can be hard to distinguish between normal variance and a regression but the results are repeatable * the links below are to the Summary section which includes throughput (absolute and relative). The relative throughput is the (throughput for PG 16 / throughput for PG 15.2) and * I used the same compiler options for the builds of 15.2 and 16 Summary of the results: * from r1 - insert-heavy (l.i0, l.i1) and create indexes (l.x) steps are ~2% slower in PG 16 * from r2 - create index (l.x) step is ~4% slower in PG 16 * from r3 - regressions are similar to r1 * from r4, r5 and r6 - regressions are mostly worse than r1, r2, r3. Note r4, r5, r6 are the same workload as r1, r2, r3 except the database is cached by PG for r1, r2, r3 so the r4, r5 and r6 benchmarks will do much more copying from the OS page cache into the Postgres buffer pool. I will repeat r1, r2, r4 and r5 but with the tests run in a different order to confirm this isn't just noise. Database cached by Postgres: r1) 1 table, 1 client - https://mdcallag.github.io/reports/23_05_04_ibench.beelink.pg16b.1u.1tno.cached/all.html#summary r2) 4 tables, 4 clients - https://mdcallag.github.io/reports/23_05_04_ibench.beelink.pg16b.4u.1tno.cached/all.html#summary r3) 1 table, 4 clients - https://mdcallag.github.io/reports/23_05_04_ibench.beelink.pg16b.4u.1tyes.cached/all.html#summary Database cached by OS but not by Postgres: r4) 1 table, 1 client - https://mdcallag.github.io/reports/23_05_04_ibench.beelink.pg16b.1u.1tno.1g/all.html#summary r5) 4 tables, 4 clients - https://mdcallag.github.io/reports/23_05_04_ibench.beelink.pg16b.4u.1tno.1g/all.html#summary r6) 1 table, 4 clients - https://mdcallag.github.io/reports/23_05_04_ibench.beelink.pg16b.4u.1tyes.1g/all.html#summary -- Mark Callaghan [email protected] ^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: benchmark results comparing versions 15.2 and 16 @ 2023-05-05 20:34 Andres Freund <[email protected]> parent: MARK CALLAGHAN <[email protected]> 1 sibling, 0 replies; 8+ messages in thread From: Andres Freund @ 2023-05-05 20:34 UTC (permalink / raw) To: MARK CALLAGHAN <[email protected]>; +Cc: [email protected] Hi, On 2023-05-05 10:45:12 -0700, MARK CALLAGHAN wrote: > This is mostly a hobby project for me - my other hobby is removing > invasive weeds. Hah :) > Summary of the results: > * from r1 - insert-heavy (l.i0, l.i1) and create indexes (l.x) steps are > ~2% slower in PG 16 > * from r2 - create index (l.x) step is ~4% slower in PG 16 > * from r3 - regressions are similar to r1 > * from r4, r5 and r6 - regressions are mostly worse than r1, r2, r3. Note > r4, r5, r6 are the same workload as r1, r2, r3 except the database is > cached by PG for r1, r2, r3 so the r4, r5 and r6 benchmarks will do much > more copying from the OS page cache into the Postgres buffer pool. One thing that's somewhat odd is that there's very marked changes in l.i0's p99 latency for the four clients cases - but whether 15 or 16 are better differs between the runs. r2) p99 20m.pg152_o3_native_lto.cx7 300 20m.pg16prebeta.cx7 23683 r3) p99 20m.pg152_o3_native_lto.cx7 70245 20m.pg16prebeta.cx7 8191 r5) p99 20m.pg152_o3_native_lto.cx7 11188 20m.pg16prebeta.cx7 72720 r6) p99 20m.pg152_o3_native_lto.cx7 1898 20m.pg16prebeta.cx7 31666 I do wonder if there's something getting scheduled in some of these runs increasing latency? Or what we're seeing depends on the time between the start of the server and the start of the benchmark? It is interesting that the per-second throughput graph shows a lot of up/down at the end: https://mdcallag.github.io/reports/23_05_04_ibench.beelink.pg16b.4u.1tyes.1g/tput.l.i0.html Both 15 and 16 have one very high result at 70s, 15 then has one low, but 16 has two low results. Greetings, Andres Freund ^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: benchmark results comparing versions 15.2 and 16 @ 2023-05-08 13:00 Alexander Lakhin <[email protected]> parent: MARK CALLAGHAN <[email protected]> 1 sibling, 1 reply; 8+ messages in thread From: Alexander Lakhin @ 2023-05-08 13:00 UTC (permalink / raw) To: MARK CALLAGHAN <[email protected]>; [email protected]; Andres Freund <[email protected]> Hello Mark, 05.05.2023 20:45, MARK CALLAGHAN wrote: > This is mostly a hobby project for me - my other hobby is removing invasive weeds. I am happy to answer questions and > run more tests, but turnaround for answers won't be instant. Getting results from Linux perf for these tests is on my > TODO list. For now I am just re-running a subset of these to get more certainty that the regressions are real and not > noise. > It's a very interesting topic to me, too. I had developed some scripts to measure and compare postgres`s performance using miscellaneous public benchmarks (ycsb, tpcds, benchmarksql_tpcc, htapbench, benchbase, gdprbench, s64da-benchmark, ...). Having compared 15.3 (56e869a09) with master (58f5edf84) I haven't seen significant regressions except a few minor ones. First regression observed with a simple pgbench test: pgbench -i benchdb pgbench -c 10 -T 300 benchdb (with default compilation options and fsync = off) On master I get: tps = 10349.826645 (without initial connection time) On 15.3: tps = 11296.064992 (without initial connection time) This difference is confirmed by multiple test runs. `git bisect` for this regression pointed at f193883fc. Best regards, Alexander ^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: benchmark results comparing versions 15.2 and 16 @ 2023-05-10 13:00 Alexander Lakhin <[email protected]> parent: Alexander Lakhin <[email protected]> 0 siblings, 1 reply; 8+ messages in thread From: Alexander Lakhin @ 2023-05-10 13:00 UTC (permalink / raw) To: MARK CALLAGHAN <[email protected]>; [email protected]; Andres Freund <[email protected]> 08.05.2023 16:00, Alexander Lakhin wrote: > ... Having compared 15.3 (56e869a09) with master > (58f5edf84) I haven't seen significant regressions except a few minor ones. > First regression observed with a simple pgbench test: Another noticeable, but not critical performance degradation is revealed by query 87 from TPC-DS (I use s64da-benchmark): https://github.com/swarm64/s64da-benchmark-toolkit/blob/master/benchmarks/tpcds/queries/queries_10/8... With `prepare_benchmark --scale-factor=2`, `run_benchmark --scale-factor=10` I get on master: 2023-05-10 09:27:52,888 INFO : finished 80/103: query 87 of stream 0: 2.26s OK but on REL_15_STABLE: 2023-05-10 08:13:40,648 INFO : finished 80/103: query 87 of stream 0: 1.94s OK This time `git bisect` pointed at 3c6fc5820. Having compared execution plans (both attached), I see the following differences (3c6fc5820~1 vs 3c6fc5820): -> Subquery Scan on "*SELECT* 1" (cost=149622.00..149958.68 rows=16834 width=21) (actual time=1018.606..1074.468 rows=93891 loops=1) -> Unique (cost=149622.00..149790.34 rows=16834 width=17) (actual time=1018.604..1064.790 rows=93891 loops=1) -> Sort (cost=149622.00..149664.09 rows=16834 width=17) (actual time=1018.603..1052.591 rows=94199 loops=1) -> Gather (cost=146588.59..148440.33 rows=16834 width=17) (actual time=880.899..913.978 rows=94199 loops=1) vs -> Subquery Scan on "*SELECT* 1" (cost=147576.79..149829.53 rows=16091 width=21) (actual time=1126.489..1366.751 rows=93891 loops=1) -> Unique (cost=147576.79..149668.62 rows=16091 width=17) (actual time=1126.487..1356.938 rows=93891 loops=1) -> Gather Merge (cost=147576.79..149547.94 rows=16091 width=17) (actual time=1126.487..1345.253 rows=94204 loops=1) -> Unique (cost=146576.78..146737.69 rows=16091 width=17) (actual time=1124.426..1306.532 rows=47102 loops=2) -> Sort (cost=146576.78..146617.01 rows=16091 width=17) (actual time=1124.424..1245.110 rows=533434 loops=2) -> Subquery Scan on "*SELECT* 2" (cost=52259.82..52428.16 rows=8417 width=21) (actual time=653.640..676.879 rows=62744 loops=1) -> Unique (cost=52259.82..52343.99 rows=8417 width=17) (actual time=653.639..670.405 rows=62744 loops=1) -> Sort (cost=52259.82..52280.86 rows=8417 width=17) (actual time=653.637..662.428 rows=62744 loops=1) -> Gather (cost=50785.20..51711.07 rows=8417 width=17) (actual time=562.158..571.737 rows=62744 loops=1) -> HashAggregate (cost=49785.20..49869.37 rows=8417 width=17) (actual time=538.263..544.336 rows=31372 loops=2) -> Nested Loop (cost=0.85..49722.07 rows=8417 width=17) (actual time=2.049..469.747 rows=284349 loops=2) vs -> Subquery Scan on "*SELECT* 2" (cost=48503.68..49630.12 rows=8046 width=21) (actual time=700.050..828.388 rows=62744 loops=1) -> Unique (cost=48503.68..49549.66 rows=8046 width=17) (actual time=700.047..821.836 rows=62744 loops=1) -> Gather Merge (cost=48503.68..49489.31 rows=8046 width=17) (actual time=700.047..814.136 rows=62744 loops=1) -> Unique (cost=47503.67..47584.13 rows=8046 width=17) (actual time=666.348..763.403 rows=31372 loops=2) -> Sort (cost=47503.67..47523.78 rows=8046 width=17) (actual time=666.347..730.336 rows=284349 loops=2) -> Nested Loop (cost=0.85..46981.72 rows=8046 width=17) (actual time=1.852..454.111 rows=284349 loops=2) -> Subquery Scan on "*SELECT* 3" (cost=50608.83..51568.70 rows=7165 width=21) (actual time=302.571..405.305 rows=23737 loops=1) -> Unique (cost=50608.83..51497.05 rows=7165 width=17) (actual time=302.568..402.818 rows=23737 loops=1) -> Gather Merge (cost=50608.83..51443.31 rows=7165 width=17) (actual time=302.567..372.246 rows=287761 loops=1) -> Sort (cost=49608.81..49616.27 rows=2985 width=17) (actual time=298.204..310.075 rows=95920 loops=3) -> Nested Loop (cost=2570.65..49436.52 rows=2985 width=17) (actual time=3.205..229.192 rows=95920 loops=3) vs -> Subquery Scan on "*SELECT* 3" (cost=50541.84..51329.11 rows=5708 width=21) (actual time=302.042..336.820 rows=23737 loops=1) -> Unique (cost=50541.84..51272.03 rows=5708 width=17) (actual time=302.039..334.329 rows=23737 loops=1) -> Gather Merge (cost=50541.84..51229.22 rows=5708 width=17) (actual time=302.039..331.296 rows=24128 loops=1) -> Unique (cost=49541.81..49570.35 rows=2854 width=17) (actual time=298.771..320.560 rows=8043 loops=3) -> Sort (cost=49541.81..49548.95 rows=2854 width=17) (actual time=298.770..309.603 rows=95920 loops=3) -> Nested Loop (cost=2570.52..49378.01 rows=2854 width=17) (actual time=3.209..230.291 rows=95920 loops=3) From the commit message and the discussion [1], it's not clear to me, whether this plan change is expected. Perhaps it's too minor issue to bring attention to, but maybe this information could be useful for v16 performance analysis. [1] https://postgr.es/m/CAApHDvo8Lz2H=42urBbfP65LTcEUOh288MT7DsG2_EWtW1AXHQ@mail.gmail.com Best regards, Alexander QUERY PLAN -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Aggregate (cost=251725.01..251725.02 rows=1 width=8) (actual time=2617.632..2636.767 rows=1 loops=1) Output: count(*) -> Subquery Scan on cool_cust (cost=147576.79..251684.78 rows=16091 width=0) (actual time=2601.794..2633.189 rows=93140 loops=1) Output: cool_cust.c_last_name, cool_cust.c_first_name, cool_cust.d_date -> HashSetOp Except (cost=147576.79..251523.87 rows=16091 width=144) (actual time=2601.792..2624.473 rows=93140 loops=1) Output: "*SELECT* 1".c_last_name, "*SELECT* 1".c_first_name, "*SELECT* 1".d_date, (0) -> Append (cost=147576.79..251360.38 rows=21799 width=144) (actual time=2213.850..2598.501 rows=117004 loops=1) -> Result (cost=147576.79..199922.27 rows=16091 width=144) (actual time=2213.850..2255.342 rows=93267 loops=1) Output: "*SELECT* 1".c_last_name, "*SELECT* 1".c_first_name, "*SELECT* 1".d_date, 0 -> HashSetOp Except (cost=147576.79..199761.36 rows=16091 width=144) (actual time=2213.848..2245.447 rows=93267 loops=1) Output: "*SELECT* 1".c_last_name, "*SELECT* 1".c_first_name, "*SELECT* 1".d_date, (0) -> Append (cost=147576.79..199580.33 rows=24137 width=144) (actual time=1126.490..2203.619 rows=156635 loops=1) -> Subquery Scan on "*SELECT* 1" (cost=147576.79..149829.53 rows=16091 width=21) (actual time=1126.489..1366.751 rows=93891 loops=1) Output: "*SELECT* 1".c_last_name, "*SELECT* 1".c_first_name, "*SELECT* 1".d_date, 0 -> Unique (cost=147576.79..149668.62 rows=16091 width=17) (actual time=1126.487..1356.938 rows=93891 loops=1) Output: customer.c_last_name, customer.c_first_name, date_dim.d_date -> Gather Merge (cost=147576.79..149547.94 rows=16091 width=17) (actual time=1126.487..1345.253 rows=94204 loops=1) Output: customer.c_last_name, customer.c_first_name, date_dim.d_date Workers Planned: 1 Workers Launched: 1 -> Unique (cost=146576.78..146737.69 rows=16091 width=17) (actual time=1124.426..1306.532 rows=47102 loops=2) Output: customer.c_last_name, customer.c_first_name, date_dim.d_date Worker 0: actual time=1122.677..1304.272 rows=47025 loops=1 -> Sort (cost=146576.78..146617.01 rows=16091 width=17) (actual time=1124.424..1245.110 rows=533434 loops=2) Output: customer.c_last_name, customer.c_first_name, date_dim.d_date Sort Key: customer.c_last_name, customer.c_first_name, date_dim.d_date Sort Method: external merge Disk: 15176kB Worker 0: actual time=1122.675..1241.764 rows=532473 loops=1 Sort Method: external merge Disk: 15136kB -> Parallel Hash Join (cost=140703.38..145452.51 rows=16091 width=17) (actual time=644.066..761.474 rows=533434 loops=2) Output: customer.c_last_name, customer.c_first_name, date_dim.d_date Hash Cond: (customer.c_customer_sk = store_sales.ss_customer_sk) Worker 0: actual time=647.901..758.829 rows=532473 loops=1 -> Parallel Seq Scan on public.customer (cost=0.00..3838.06 rows=84706 width=17) (actual time=0.009..6.282 rows=72000 loops=2) Output: customer.c_customer_sk, customer.c_customer_id, customer.c_current_cdemo_sk, customer.c_current_hdemo_sk, customer.c_current_addr_sk, customer.c_first_shipto_date_sk, customer.c_first_sales_date_sk, customer.c_salutation, customer.c_first_name, customer.c_last_name, customer.c_preferred_cust_flag, customer.c_birth_day, customer.c_birth_month, customer.c_birth_year, customer.c_birth_country, customer.c_login, customer.c_email_address, customer.c_last_review_date_sk Worker 0: actual time=0.009..6.398 rows=71611 loops=1 -> Parallel Hash (cost=140560.90..140560.90 rows=11398 width=8) (actual time=617.582..617.586 rows=546248 loops=2) Output: store_sales.ss_customer_sk, date_dim.d_date Buckets: 262144 (originally 32768) Batches: 8 (originally 1) Memory Usage: 7360kB Worker 0: actual time=616.651..616.654 rows=547248 loops=1 -> Parallel Hash Join (cost=2570.10..140560.90 rows=11398 width=8) (actual time=5.390..508.118 rows=546248 loops=2) Output: store_sales.ss_customer_sk, date_dim.d_date Inner Unique: true Hash Cond: (store_sales.ss_sold_date_sk = date_dim.d_date_sk) Worker 0: actual time=4.456..507.951 rows=547248 loops=1 -> Parallel Seq Scan on public.store_sales (cost=0.00..131691.82 rows=2399482 width=8) (actual time=0.018..260.792 rows=2879322 loops=2) Output: store_sales.ss_sold_date_sk, store_sales.ss_sold_time_sk, store_sales.ss_item_sk, store_sales.ss_customer_sk, store_sales.ss_cdemo_sk, store_sales.ss_hdemo_sk, store_sales.ss_addr_sk, store_sales.ss_store_sk, store_sales.ss_promo_sk, store_sales.ss_ticket_number, store_sales.ss_quantity, store_sales.ss_wholesale_cost, store_sales.ss_list_price, store_sales.ss_sales_price, store_sales.ss_ext_discount_amt, store_sales.ss_ext_sales_price, store_sales.ss_ext_wholesale_cost, store_sales.ss_ext_list_price, store_sales.ss_ext_tax, store_sales.ss_coupon_amt, store_sales.ss_net_paid, store_sales.ss_net_paid_inc_tax, store_sales.ss_net_profit Worker 0: actual time=0.020..261.441 rows=2887593 loops=1 -> Parallel Hash (cost=2567.55..2567.55 rows=204 width=8) (actual time=5.352..5.353 rows=182 loops=2) Output: date_dim.d_date, date_dim.d_date_sk Buckets: 1024 Batches: 1 Memory Usage: 72kB Worker 0: actual time=4.409..4.410 rows=152 loops=1 -> Parallel Seq Scan on public.date_dim (cost=0.00..2567.55 rows=204 width=8) (actual time=2.560..5.299 rows=182 loops=2) Output: date_dim.d_date, date_dim.d_date_sk Filter: ((date_dim.d_month_seq >= 1176) AND (date_dim.d_month_seq <= 1187)) Rows Removed by Filter: 36342 Worker 0: actual time=1.621..4.358 rows=152 loops=1 -> Subquery Scan on "*SELECT* 2" (cost=48503.68..49630.12 rows=8046 width=21) (actual time=700.050..828.388 rows=62744 loops=1) Output: "*SELECT* 2".c_last_name, "*SELECT* 2".c_first_name, "*SELECT* 2".d_date, 1 -> Unique (cost=48503.68..49549.66 rows=8046 width=17) (actual time=700.047..821.836 rows=62744 loops=1) Output: customer_1.c_last_name, customer_1.c_first_name, date_dim_1.d_date -> Gather Merge (cost=48503.68..49489.31 rows=8046 width=17) (actual time=700.047..814.136 rows=62744 loops=1) Output: customer_1.c_last_name, customer_1.c_first_name, date_dim_1.d_date Workers Planned: 1 Workers Launched: 1 -> Unique (cost=47503.67..47584.13 rows=8046 width=17) (actual time=666.348..763.403 rows=31372 loops=2) Output: customer_1.c_last_name, customer_1.c_first_name, date_dim_1.d_date Worker 0: actual time=632.937..724.548 rows=29426 loops=1 -> Sort (cost=47503.67..47523.78 rows=8046 width=17) (actual time=666.347..730.336 rows=284349 loops=2) Output: customer_1.c_last_name, customer_1.c_first_name, date_dim_1.d_date Sort Key: customer_1.c_last_name, customer_1.c_first_name, date_dim_1.d_date Sort Method: external merge Disk: 8576kB Worker 0: actual time=632.936..692.503 rows=266513 loops=1 Sort Method: external merge Disk: 7576kB -> Nested Loop (cost=0.85..46981.72 rows=8046 width=17) (actual time=1.852..454.111 rows=284349 loops=2) Output: customer_1.c_last_name, customer_1.c_first_name, date_dim_1.d_date Inner Unique: true Worker 0: actual time=0.681..433.435 rows=266513 loops=1 -> Nested Loop (cost=0.43..43423.72 rows=8046 width=8) (actual time=1.829..88.512 rows=285052 loops=2) Output: catalog_sales.cs_bill_customer_sk, date_dim_1.d_date Worker 0: actual time=0.648..85.747 rows=267146 loops=1 -> Parallel Seq Scan on public.date_dim date_dim_1 (cost=0.00..2567.55 rows=204 width=8) (actual time=1.789..3.883 rows=182 loops=2) Output: date_dim_1.d_date_sk, date_dim_1.d_date_id, date_dim_1.d_date, date_dim_1.d_month_seq, date_dim_1.d_week_seq, date_dim_1.d_quarter_seq, date_dim_1.d_year, date_dim_1.d_dow, date_dim_1.d_moy, date_dim_1.d_dom, date_dim_1.d_qoy, date_dim_1.d_fy_year, date_dim_1.d_fy_quarter_seq, date_dim_1.d_fy_week_seq, date_dim_1.d_day_name, date_dim_1.d_quarter_name, date_dim_1.d_holiday, date_dim_1.d_weekend, date_dim_1.d_following_holiday, date_dim_1.d_first_dom, date_dim_1.d_last_dom, date_dim_1.d_same_day_ly, date_dim_1.d_same_day_lq, date_dim_1.d_current_day, date_dim_1.d_current_week, date_dim_1.d_current_month, date_dim_1.d_current_quarter, date_dim_1.d_current_year Filter: ((date_dim_1.d_month_seq >= 1176) AND (date_dim_1.d_month_seq <= 1187)) Rows Removed by Filter: 36342 Worker 0: actual time=0.597..4.725 rows=172 loops=1 -> Index Scan using idx_cs_sold_date_sk on public.catalog_sales (cost=0.43..184.68 rows=1560 width=8) (actual time=0.004..0.317 rows=1562 loops=365) Output: catalog_sales.cs_sold_date_sk, catalog_sales.cs_sold_time_sk, catalog_sales.cs_ship_date_sk, catalog_sales.cs_bill_customer_sk, catalog_sales.cs_bill_cdemo_sk, catalog_sales.cs_bill_hdemo_sk, catalog_sales.cs_bill_addr_sk, catalog_sales.cs_ship_customer_sk, catalog_sales.cs_ship_cdemo_sk, catalog_sales.cs_ship_hdemo_sk, catalog_sales.cs_ship_addr_sk, catalog_sales.cs_call_center_sk, catalog_sales.cs_catalog_page_sk, catalog_sales.cs_ship_mode_sk, catalog_sales.cs_warehouse_sk, catalog_sales.cs_item_sk, catalog_sales.cs_promo_sk, catalog_sales.cs_order_number, catalog_sales.cs_quantity, catalog_sales.cs_wholesale_cost, catalog_sales.cs_list_price, catalog_sales.cs_sales_price, catalog_sales.cs_ext_discount_amt, catalog_sales.cs_ext_sales_price, catalog_sales.cs_ext_wholesale_cost, catalog_sales.cs_ext_list_price, catalog_sales.cs_ext_tax, catalog_sales.cs_coupon_amt, catalog_sales.cs_ext_ship_cost, catalog_sales.cs_net_paid, catalog_sales.cs_net_paid_inc_tax, catalog_sales.cs_net_paid_inc_ship, catalog_sales.cs_net_paid_inc_ship_tax, catalog_sales.cs_net_profit Index Cond: (catalog_sales.cs_sold_date_sk = date_dim_1.d_date_sk) Worker 0: actual time=0.004..0.323 rows=1553 loops=172 -> Index Scan using customer_pkey on public.customer customer_1 (cost=0.42..0.44 rows=1 width=17) (actual time=0.001..0.001 rows=1 loops=570105) Output: customer_1.c_customer_sk, customer_1.c_customer_id, customer_1.c_current_cdemo_sk, customer_1.c_current_hdemo_sk, customer_1.c_current_addr_sk, customer_1.c_first_shipto_date_sk, customer_1.c_first_sales_date_sk, customer_1.c_salutation, customer_1.c_first_name, customer_1.c_last_name, customer_1.c_preferred_cust_flag, customer_1.c_birth_day, customer_1.c_birth_month, customer_1.c_birth_year, customer_1.c_birth_country, customer_1.c_login, customer_1.c_email_address, customer_1.c_last_review_date_sk Index Cond: (customer_1.c_customer_sk = catalog_sales.cs_bill_customer_sk) Worker 0: actual time=0.001..0.001 rows=1 loops=267146 -> Subquery Scan on "*SELECT* 3" (cost=50541.84..51329.11 rows=5708 width=21) (actual time=302.042..336.820 rows=23737 loops=1) Output: "*SELECT* 3".c_last_name, "*SELECT* 3".c_first_name, "*SELECT* 3".d_date, 1 -> Unique (cost=50541.84..51272.03 rows=5708 width=17) (actual time=302.039..334.329 rows=23737 loops=1) Output: customer_2.c_last_name, customer_2.c_first_name, date_dim_2.d_date -> Gather Merge (cost=50541.84..51229.22 rows=5708 width=17) (actual time=302.039..331.296 rows=24128 loops=1) Output: customer_2.c_last_name, customer_2.c_first_name, date_dim_2.d_date Workers Planned: 2 Workers Launched: 2 -> Unique (cost=49541.81..49570.35 rows=2854 width=17) (actual time=298.771..320.560 rows=8043 loops=3) Output: customer_2.c_last_name, customer_2.c_first_name, date_dim_2.d_date Worker 0: actual time=297.820..319.743 rows=7997 loops=1 Worker 1: actual time=296.746..318.198 rows=7891 loops=1 -> Sort (cost=49541.81..49548.95 rows=2854 width=17) (actual time=298.770..309.603 rows=95920 loops=3) Output: customer_2.c_last_name, customer_2.c_first_name, date_dim_2.d_date Sort Key: customer_2.c_last_name, customer_2.c_first_name, date_dim_2.d_date Sort Method: external merge Disk: 2792kB Worker 0: actual time=297.818..308.737 rows=95675 loops=1 Sort Method: external merge Disk: 2720kB Worker 1: actual time=296.745..307.315 rows=93910 loops=1 Sort Method: external merge Disk: 2664kB -> Nested Loop (cost=2570.52..49378.01 rows=2854 width=17) (actual time=3.209..230.291 rows=95920 loops=3) Output: customer_2.c_last_name, customer_2.c_first_name, date_dim_2.d_date Inner Unique: true Worker 0: actual time=2.294..228.843 rows=95675 loops=1 Worker 1: actual time=2.670..230.164 rows=93910 loops=1 -> Parallel Hash Join (cost=2570.10..48102.56 rows=2854 width=8) (actual time=3.191..99.159 rows=95936 loops=3) Output: web_sales.ws_bill_customer_sk, date_dim_2.d_date Inner Unique: true Hash Cond: (web_sales.ws_sold_date_sk = date_dim_2.d_date_sk) Worker 0: actual time=2.270..97.201 rows=95690 loops=1 Worker 1: actual time=2.653..98.648 rows=93924 loops=1 -> Parallel Seq Scan on public.web_sales (cost=0.00..43955.21 rows=600821 width=8) (actual time=0.017..53.280 rows=480649 loops=3) Output: web_sales.ws_sold_date_sk, web_sales.ws_sold_time_sk, web_sales.ws_ship_date_sk, web_sales.ws_item_sk, web_sales.ws_bill_customer_sk, web_sales.ws_bill_cdemo_sk, web_sales.ws_bill_hdemo_sk, web_sales.ws_bill_addr_sk, web_sales.ws_ship_customer_sk, web_sales.ws_ship_cdemo_sk, web_sales.ws_ship_hdemo_sk, web_sales.ws_ship_addr_sk, web_sales.ws_web_page_sk, web_sales.ws_web_site_sk, web_sales.ws_ship_mode_sk, web_sales.ws_warehouse_sk, web_sales.ws_promo_sk, web_sales.ws_order_number, web_sales.ws_quantity, web_sales.ws_wholesale_cost, web_sales.ws_list_price, web_sales.ws_sales_price, web_sales.ws_ext_discount_amt, web_sales.ws_ext_sales_price, web_sales.ws_ext_wholesale_cost, web_sales.ws_ext_list_price, web_sales.ws_ext_tax, web_sales.ws_coupon_amt, web_sales.ws_ext_ship_cost, web_sales.ws_net_paid, web_sales.ws_net_paid_inc_tax, web_sales.ws_net_paid_inc_ship, web_sales.ws_net_paid_inc_ship_tax, web_sales.ws_net_profit Worker 0: actual time=0.023..52.210 rows=480453 loops=1 Worker 1: actual time=0.020..54.103 rows=473734 loops=1 -> Parallel Hash (cost=2567.55..2567.55 rows=204 width=8) (actual time=3.032..3.032 rows=122 loops=3) Output: date_dim_2.d_date, date_dim_2.d_date_sk Buckets: 1024 Batches: 1 Memory Usage: 104kB Worker 0: actual time=2.170..2.171 rows=76 loops=1 Worker 1: actual time=2.559..2.560 rows=96 loops=1 -> Parallel Seq Scan on public.date_dim date_dim_2 (cost=0.00..2567.55 rows=204 width=8) (actual time=1.329..2.976 rows=122 loops=3) Output: date_dim_2.d_date, date_dim_2.d_date_sk Filter: ((date_dim_2.d_month_seq >= 1176) AND (date_dim_2.d_month_seq <= 1187)) Rows Removed by Filter: 24228 Worker 0: actual time=0.464..2.104 rows=76 loops=1 Worker 1: actual time=0.857..2.506 rows=96 loops=1 -> Index Scan using customer_pkey on public.customer customer_2 (cost=0.42..0.45 rows=1 width=17) (actual time=0.001..0.001 rows=1 loops=287809) Output: customer_2.c_customer_sk, customer_2.c_customer_id, customer_2.c_current_cdemo_sk, customer_2.c_current_hdemo_sk, customer_2.c_current_addr_sk, customer_2.c_first_shipto_date_sk, customer_2.c_first_sales_date_sk, customer_2.c_salutation, customer_2.c_first_name, customer_2.c_last_name, customer_2.c_preferred_cust_flag, customer_2.c_birth_day, customer_2.c_birth_month, customer_2.c_birth_year, customer_2.c_birth_country, customer_2.c_login, customer_2.c_email_address, customer_2.c_last_review_date_sk Index Cond: (customer_2.c_customer_sk = web_sales.ws_bill_customer_sk) Worker 0: actual time=0.001..0.001 rows=1 loops=95690 Worker 1: actual time=0.001..0.001 rows=1 loops=93924 Planning Time: 2.926 ms Execution Time: 2642.408 ms (147 rows) QUERY PLAN -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Aggregate (cost=254949.93..254949.94 rows=1 width=8) (actual time=2242.324..2259.657 rows=1 loops=1) Output: count(*) -> Subquery Scan on cool_cust (cost=149622.00..254907.85 rows=16834 width=0) (actual time=2226.567..2256.123 rows=93140 loops=1) Output: cool_cust.c_last_name, cool_cust.c_first_name, cool_cust.d_date -> HashSetOp Except (cost=149622.00..254739.51 rows=16834 width=144) (actual time=2226.566..2247.422 rows=93140 loops=1) Output: "*SELECT* 1".c_last_name, "*SELECT* 1".c_first_name, "*SELECT* 1".d_date, (0) -> Append (cost=149622.00..254559.51 rows=23999 width=144) (actual time=1771.245..2221.971 rows=117004 loops=1) -> Result (cost=149622.00..202870.82 rows=16834 width=144) (actual time=1771.245..1810.319 rows=93267 loops=1) Output: "*SELECT* 1".c_last_name, "*SELECT* 1".c_first_name, "*SELECT* 1".d_date, 0 -> HashSetOp Except (cost=149622.00..202702.48 rows=16834 width=144) (actual time=1771.244..1800.321 rows=93267 loops=1) Output: "*SELECT* 1".c_last_name, "*SELECT* 1".c_first_name, "*SELECT* 1".d_date, (0) -> Append (cost=149622.00..202513.10 rows=25251 width=144) (actual time=1018.607..1759.828 rows=156635 loops=1) -> Subquery Scan on "*SELECT* 1" (cost=149622.00..149958.68 rows=16834 width=21) (actual time=1018.606..1074.468 rows=93891 loops=1) Output: "*SELECT* 1".c_last_name, "*SELECT* 1".c_first_name, "*SELECT* 1".d_date, 0 -> Unique (cost=149622.00..149790.34 rows=16834 width=17) (actual time=1018.604..1064.790 rows=93891 loops=1) Output: customer.c_last_name, customer.c_first_name, date_dim.d_date -> Sort (cost=149622.00..149664.09 rows=16834 width=17) (actual time=1018.603..1052.591 rows=94199 loops=1) Output: customer.c_last_name, customer.c_first_name, date_dim.d_date Sort Key: customer.c_last_name, customer.c_first_name, date_dim.d_date Sort Method: external merge Disk: 2696kB -> Gather (cost=146588.59..148440.33 rows=16834 width=17) (actual time=880.899..913.978 rows=94199 loops=1) Output: customer.c_last_name, customer.c_first_name, date_dim.d_date Workers Planned: 1 Workers Launched: 1 -> HashAggregate (cost=145588.59..145756.93 rows=16834 width=17) (actual time=879.119..887.352 rows=47100 loops=2) Output: customer.c_last_name, customer.c_first_name, date_dim.d_date Group Key: customer.c_last_name, customer.c_first_name, date_dim.d_date Batches: 1 Memory Usage: 4625kB Worker 0: actual time=877.609..886.041 rows=44455 loops=1 Batches: 1 Memory Usage: 4369kB -> Parallel Hash Join (cost=140710.34..145462.34 rows=16834 width=17) (actual time=644.707..767.476 rows=533434 loops=2) Output: customer.c_last_name, customer.c_first_name, date_dim.d_date Hash Cond: (customer.c_customer_sk = store_sales.ss_customer_sk) Worker 0: actual time=650.882..772.536 rows=503226 loops=1 -> Parallel Seq Scan on public.customer (cost=0.00..3838.06 rows=84706 width=17) (actual time=0.009..6.004 rows=72000 loops=2) Output: customer.c_customer_sk, customer.c_customer_id, customer.c_current_cdemo_sk, customer.c_current_hdemo_sk, customer.c_current_addr_sk, customer.c_first_shipto_date_sk, customer.c_first_sales_date_sk, customer.c_salutation, customer.c_first_name, customer.c_last_name, customer.c_preferred_cust_flag, customer.c_birth_day, customer.c_birth_month, customer.c_birth_year, customer.c_birth_country, customer.c_login, customer.c_email_address, customer.c_last_review_date_sk Worker 0: actual time=0.009..6.171 rows=70942 loops=1 -> Parallel Hash (cost=140561.29..140561.29 rows=11924 width=8) (actual time=615.499..615.504 rows=546248 loops=2) Output: store_sales.ss_customer_sk, date_dim.d_date Buckets: 262144 (originally 32768) Batches: 8 (originally 1) Memory Usage: 7360kB Worker 0: actual time=614.329..614.333 rows=542130 loops=1 -> Parallel Hash Join (cost=2570.23..140561.29 rows=11924 width=8) (actual time=5.364..506.303 rows=546248 loops=2) Output: store_sales.ss_customer_sk, date_dim.d_date Inner Unique: true Hash Cond: (store_sales.ss_sold_date_sk = date_dim.d_date_sk) Worker 0: actual time=4.176..503.249 rows=542130 loops=1 -> Parallel Seq Scan on public.store_sales (cost=0.00..131692.02 rows=2399502 width=8) (actual time=0.017..258.551 rows=2879322 loops=2) Output: store_sales.ss_sold_date_sk, store_sales.ss_sold_time_sk, store_sales.ss_item_sk, store_sales.ss_customer_sk, store_sales.ss_cdemo_sk, store_sales.ss_hdemo_sk, store_sales.ss_addr_sk, store_sales.ss_store_sk, store_sales.ss_promo_sk, store_sales.ss_ticket_number, store_sales.ss_quantity, store_sales.ss_wholesale_cost, store_sales.ss_list_price, store_sales.ss_sales_price, store_sales.ss_ext_discount_amt, store_sales.ss_ext_sales_price, store_sales.ss_ext_wholesale_cost, store_sales.ss_ext_list_price, store_sales.ss_ext_tax, store_sales.ss_coupon_amt, store_sales.ss_net_paid, store_sales.ss_net_paid_inc_tax, store_sales.ss_net_profit Worker 0: actual time=0.021..258.112 rows=2861258 loops=1 -> Parallel Hash (cost=2567.55..2567.55 rows=214 width=8) (actual time=5.302..5.303 rows=182 loops=2) Output: date_dim.d_date, date_dim.d_date_sk Buckets: 1024 Batches: 1 Memory Usage: 72kB Worker 0: actual time=4.121..4.122 rows=172 loops=1 -> Parallel Seq Scan on public.date_dim (cost=0.00..2567.55 rows=214 width=8) (actual time=2.697..5.256 rows=182 loops=2) Output: date_dim.d_date, date_dim.d_date_sk Filter: ((date_dim.d_month_seq >= 1176) AND (date_dim.d_month_seq <= 1187)) Rows Removed by Filter: 36342 Worker 0: actual time=1.520..4.075 rows=172 loops=1 -> Subquery Scan on "*SELECT* 2" (cost=52259.82..52428.16 rows=8417 width=21) (actual time=653.640..676.879 rows=62744 loops=1) Output: "*SELECT* 2".c_last_name, "*SELECT* 2".c_first_name, "*SELECT* 2".d_date, 1 -> Unique (cost=52259.82..52343.99 rows=8417 width=17) (actual time=653.639..670.405 rows=62744 loops=1) Output: customer_1.c_last_name, customer_1.c_first_name, date_dim_1.d_date -> Sort (cost=52259.82..52280.86 rows=8417 width=17) (actual time=653.637..662.428 rows=62744 loops=1) Output: customer_1.c_last_name, customer_1.c_first_name, date_dim_1.d_date Sort Key: customer_1.c_last_name, customer_1.c_first_name, date_dim_1.d_date Sort Method: external merge Disk: 1800kB -> Gather (cost=50785.20..51711.07 rows=8417 width=17) (actual time=562.158..571.737 rows=62744 loops=1) Output: customer_1.c_last_name, customer_1.c_first_name, date_dim_1.d_date Workers Planned: 1 Workers Launched: 1 -> HashAggregate (cost=49785.20..49869.37 rows=8417 width=17) (actual time=538.263..544.336 rows=31372 loops=2) Output: customer_1.c_last_name, customer_1.c_first_name, date_dim_1.d_date Group Key: customer_1.c_last_name, customer_1.c_first_name, date_dim_1.d_date Batches: 1 Memory Usage: 3601kB Worker 0: actual time=514.615..520.864 rows=29639 loops=1 Batches: 1 Memory Usage: 3601kB -> Nested Loop (cost=0.85..49722.07 rows=8417 width=17) (actual time=2.049..469.747 rows=284349 loops=2) Output: customer_1.c_last_name, customer_1.c_first_name, date_dim_1.d_date Inner Unique: true Worker 0: actual time=0.961..449.691 rows=268480 loops=1 -> Nested Loop (cost=0.43..46000.01 rows=8417 width=8) (actual time=2.029..93.688 rows=285052 loops=2) Output: catalog_sales.cs_bill_customer_sk, date_dim_1.d_date Worker 0: actual time=0.932..90.920 rows=269122 loops=1 -> Parallel Seq Scan on public.date_dim date_dim_1 (cost=0.00..2567.55 rows=214 width=8) (actual time=1.983..4.233 rows=182 loops=2) Output: date_dim_1.d_date_sk, date_dim_1.d_date_id, date_dim_1.d_date, date_dim_1.d_month_seq, date_dim_1.d_week_seq, date_dim_1.d_quarter_seq, date_dim_1.d_year, date_dim_1.d_dow, date_dim_1.d_moy, date_dim_1.d_dom, date_dim_1.d_qoy, date_dim_1.d_fy_year, date_dim_1.d_fy_quarter_seq, date_dim_1.d_fy_week_seq, date_dim_1.d_day_name, date_dim_1.d_quarter_name, date_dim_1.d_holiday, date_dim_1.d_weekend, date_dim_1.d_following_holiday, date_dim_1.d_first_dom, date_dim_1.d_last_dom, date_dim_1.d_same_day_ly, date_dim_1.d_same_day_lq, date_dim_1.d_current_day, date_dim_1.d_current_week, date_dim_1.d_current_month, date_dim_1.d_current_quarter, date_dim_1.d_current_year Filter: ((date_dim_1.d_month_seq >= 1176) AND (date_dim_1.d_month_seq <= 1187)) Rows Removed by Filter: 36342 Worker 0: actual time=0.886..5.324 rows=175 loops=1 -> Index Scan using idx_cs_sold_date_sk on public.catalog_sales (cost=0.43..187.36 rows=1560 width=8) (actual time=0.004..0.341 rows=1562 loops=365) Output: catalog_sales.cs_sold_date_sk, catalog_sales.cs_sold_time_sk, catalog_sales.cs_ship_date_sk, catalog_sales.cs_bill_customer_sk, catalog_sales.cs_bill_cdemo_sk, catalog_sales.cs_bill_hdemo_sk, catalog_sales.cs_bill_addr_sk, catalog_sales.cs_ship_customer_sk, catalog_sales.cs_ship_cdemo_sk, catalog_sales.cs_ship_hdemo_sk, catalog_sales.cs_ship_addr_sk, catalog_sales.cs_call_center_sk, catalog_sales.cs_catalog_page_sk, catalog_sales.cs_ship_mode_sk, catalog_sales.cs_warehouse_sk, catalog_sales.cs_item_sk, catalog_sales.cs_promo_sk, catalog_sales.cs_order_number, catalog_sales.cs_quantity, catalog_sales.cs_wholesale_cost, catalog_sales.cs_list_price, catalog_sales.cs_sales_price, catalog_sales.cs_ext_discount_amt, catalog_sales.cs_ext_sales_price, catalog_sales.cs_ext_wholesale_cost, catalog_sales.cs_ext_list_price, catalog_sales.cs_ext_tax, catalog_sales.cs_coupon_amt, catalog_sales.cs_ext_ship_cost, catalog_sales.cs_net_paid, catalog_sales.cs_net_paid_inc_tax, catalog_sales.cs_net_paid_inc_ship, catalog_sales.cs_net_paid_inc_ship_tax, catalog_sales.cs_net_profit Index Cond: (catalog_sales.cs_sold_date_sk = date_dim_1.d_date_sk) Worker 0: actual time=0.005..0.342 rows=1538 loops=175 -> Index Scan using customer_pkey on public.customer customer_1 (cost=0.42..0.44 rows=1 width=17) (actual time=0.001..0.001 rows=1 loops=570105) Output: customer_1.c_customer_sk, customer_1.c_customer_id, customer_1.c_current_cdemo_sk, customer_1.c_current_hdemo_sk, customer_1.c_current_addr_sk, customer_1.c_first_shipto_date_sk, customer_1.c_first_sales_date_sk, customer_1.c_salutation, customer_1.c_first_name, customer_1.c_last_name, customer_1.c_preferred_cust_flag, customer_1.c_birth_day, customer_1.c_birth_month, customer_1.c_birth_year, customer_1.c_birth_country, customer_1.c_login, customer_1.c_email_address, customer_1.c_last_review_date_sk Index Cond: (customer_1.c_customer_sk = catalog_sales.cs_bill_customer_sk) Worker 0: actual time=0.001..0.001 rows=1 loops=269122 -> Subquery Scan on "*SELECT* 3" (cost=50608.83..51568.70 rows=7165 width=21) (actual time=302.571..405.305 rows=23737 loops=1) Output: "*SELECT* 3".c_last_name, "*SELECT* 3".c_first_name, "*SELECT* 3".d_date, 1 -> Unique (cost=50608.83..51497.05 rows=7165 width=17) (actual time=302.568..402.818 rows=23737 loops=1) Output: customer_2.c_last_name, customer_2.c_first_name, date_dim_2.d_date -> Gather Merge (cost=50608.83..51443.31 rows=7165 width=17) (actual time=302.567..372.246 rows=287761 loops=1) Output: customer_2.c_last_name, customer_2.c_first_name, date_dim_2.d_date Workers Planned: 2 Workers Launched: 2 -> Sort (cost=49608.81..49616.27 rows=2985 width=17) (actual time=298.204..310.075 rows=95920 loops=3) Output: customer_2.c_last_name, customer_2.c_first_name, date_dim_2.d_date Sort Key: customer_2.c_last_name, customer_2.c_first_name, date_dim_2.d_date Sort Method: external merge Disk: 2824kB Worker 0: actual time=297.401..308.205 rows=96226 loops=1 Sort Method: external merge Disk: 2736kB Worker 1: actual time=294.914..308.683 rows=92128 loops=1 Sort Method: external merge Disk: 2624kB -> Nested Loop (cost=2570.65..49436.52 rows=2985 width=17) (actual time=3.205..229.192 rows=95920 loops=3) Output: customer_2.c_last_name, customer_2.c_first_name, date_dim_2.d_date Inner Unique: true Worker 0: actual time=2.263..227.978 rows=96226 loops=1 Worker 1: actual time=2.608..228.161 rows=92128 loops=1 -> Parallel Hash Join (cost=2570.23..48102.52 rows=2985 width=8) (actual time=3.186..100.585 rows=95936 loops=3) Output: web_sales.ws_bill_customer_sk, date_dim_2.d_date Inner Unique: true Hash Cond: (web_sales.ws_sold_date_sk = date_dim_2.d_date_sk) Worker 0: actual time=2.240..100.451 rows=96240 loops=1 Worker 1: actual time=2.587..98.637 rows=92152 loops=1 -> Parallel Seq Scan on public.web_sales (cost=0.00..43955.08 rows=600808 width=8) (actual time=0.017..53.842 rows=480649 loops=3) Output: web_sales.ws_sold_date_sk, web_sales.ws_sold_time_sk, web_sales.ws_ship_date_sk, web_sales.ws_item_sk, web_sales.ws_bill_customer_sk, web_sales.ws_bill_cdemo_sk, web_sales.ws_bill_hdemo_sk, web_sales.ws_bill_addr_sk, web_sales.ws_ship_customer_sk, web_sales.ws_ship_cdemo_sk, web_sales.ws_ship_hdemo_sk, web_sales.ws_ship_addr_sk, web_sales.ws_web_page_sk, web_sales.ws_web_site_sk, web_sales.ws_ship_mode_sk, web_sales.ws_warehouse_sk, web_sales.ws_promo_sk, web_sales.ws_order_number, web_sales.ws_quantity, web_sales.ws_wholesale_cost, web_sales.ws_list_price, web_sales.ws_sales_price, web_sales.ws_ext_discount_amt, web_sales.ws_ext_sales_price, web_sales.ws_ext_wholesale_cost, web_sales.ws_ext_list_price, web_sales.ws_ext_tax, web_sales.ws_coupon_amt, web_sales.ws_ext_ship_cost, web_sales.ws_net_paid, web_sales.ws_net_paid_inc_tax, web_sales.ws_net_paid_inc_ship, web_sales.ws_net_paid_inc_ship_tax, web_sales.ws_net_profit Worker 0: actual time=0.021..53.860 rows=486377 loops=1 Worker 1: actual time=0.022..54.070 rows=460098 loops=1 -> Parallel Hash (cost=2567.55..2567.55 rows=214 width=8) (actual time=3.007..3.008 rows=122 loops=3) Output: date_dim_2.d_date, date_dim_2.d_date_sk Buckets: 1024 Batches: 1 Memory Usage: 104kB Worker 0: actual time=2.123..2.123 rows=76 loops=1 Worker 1: actual time=2.433..2.434 rows=114 loops=1 -> Parallel Seq Scan on public.date_dim date_dim_2 (cost=0.00..2567.55 rows=214 width=8) (actual time=1.326..2.956 rows=122 loops=3) Output: date_dim_2.d_date, date_dim_2.d_date_sk Filter: ((date_dim_2.d_month_seq >= 1176) AND (date_dim_2.d_month_seq <= 1187)) Rows Removed by Filter: 24228 Worker 0: actual time=0.438..2.064 rows=76 loops=1 Worker 1: actual time=0.751..2.375 rows=114 loops=1 -> Index Scan using customer_pkey on public.customer customer_2 (cost=0.42..0.45 rows=1 width=17) (actual time=0.001..0.001 rows=1 loops=287809) Output: customer_2.c_customer_sk, customer_2.c_customer_id, customer_2.c_current_cdemo_sk, customer_2.c_current_hdemo_sk, customer_2.c_current_addr_sk, customer_2.c_first_shipto_date_sk, customer_2.c_first_sales_date_sk, customer_2.c_salutation, customer_2.c_first_name, customer_2.c_last_name, customer_2.c_preferred_cust_flag, customer_2.c_birth_day, customer_2.c_birth_month, customer_2.c_birth_year, customer_2.c_birth_country, customer_2.c_login, customer_2.c_email_address, customer_2.c_last_review_date_sk Index Cond: (customer_2.c_customer_sk = web_sales.ws_bill_customer_sk) Worker 0: actual time=0.001..0.001 rows=1 loops=96240 Worker 1: actual time=0.001..0.001 rows=1 loops=92152 Planning Time: 3.040 ms Execution Time: 2262.778 ms (145 rows) Attachments: [text/plain] tpcds-q87-3c6fc5820-plan.txt (26.0K, ../../[email protected]/2-tpcds-q87-3c6fc5820-plan.txt) download | inline: QUERY PLAN -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Aggregate (cost=251725.01..251725.02 rows=1 width=8) (actual time=2617.632..2636.767 rows=1 loops=1) Output: count(*) -> Subquery Scan on cool_cust (cost=147576.79..251684.78 rows=16091 width=0) (actual time=2601.794..2633.189 rows=93140 loops=1) Output: cool_cust.c_last_name, cool_cust.c_first_name, cool_cust.d_date -> HashSetOp Except (cost=147576.79..251523.87 rows=16091 width=144) (actual time=2601.792..2624.473 rows=93140 loops=1) Output: "*SELECT* 1".c_last_name, "*SELECT* 1".c_first_name, "*SELECT* 1".d_date, (0) -> Append (cost=147576.79..251360.38 rows=21799 width=144) (actual time=2213.850..2598.501 rows=117004 loops=1) -> Result (cost=147576.79..199922.27 rows=16091 width=144) (actual time=2213.850..2255.342 rows=93267 loops=1) Output: "*SELECT* 1".c_last_name, "*SELECT* 1".c_first_name, "*SELECT* 1".d_date, 0 -> HashSetOp Except (cost=147576.79..199761.36 rows=16091 width=144) (actual time=2213.848..2245.447 rows=93267 loops=1) Output: "*SELECT* 1".c_last_name, "*SELECT* 1".c_first_name, "*SELECT* 1".d_date, (0) -> Append (cost=147576.79..199580.33 rows=24137 width=144) (actual time=1126.490..2203.619 rows=156635 loops=1) -> Subquery Scan on "*SELECT* 1" (cost=147576.79..149829.53 rows=16091 width=21) (actual time=1126.489..1366.751 rows=93891 loops=1) Output: "*SELECT* 1".c_last_name, "*SELECT* 1".c_first_name, "*SELECT* 1".d_date, 0 -> Unique (cost=147576.79..149668.62 rows=16091 width=17) (actual time=1126.487..1356.938 rows=93891 loops=1) Output: customer.c_last_name, customer.c_first_name, date_dim.d_date -> Gather Merge (cost=147576.79..149547.94 rows=16091 width=17) (actual time=1126.487..1345.253 rows=94204 loops=1) Output: customer.c_last_name, customer.c_first_name, date_dim.d_date Workers Planned: 1 Workers Launched: 1 -> Unique (cost=146576.78..146737.69 rows=16091 width=17) (actual time=1124.426..1306.532 rows=47102 loops=2) Output: customer.c_last_name, customer.c_first_name, date_dim.d_date Worker 0: actual time=1122.677..1304.272 rows=47025 loops=1 -> Sort (cost=146576.78..146617.01 rows=16091 width=17) (actual time=1124.424..1245.110 rows=533434 loops=2) Output: customer.c_last_name, customer.c_first_name, date_dim.d_date Sort Key: customer.c_last_name, customer.c_first_name, date_dim.d_date Sort Method: external merge Disk: 15176kB Worker 0: actual time=1122.675..1241.764 rows=532473 loops=1 Sort Method: external merge Disk: 15136kB -> Parallel Hash Join (cost=140703.38..145452.51 rows=16091 width=17) (actual time=644.066..761.474 rows=533434 loops=2) Output: customer.c_last_name, customer.c_first_name, date_dim.d_date Hash Cond: (customer.c_customer_sk = store_sales.ss_customer_sk) Worker 0: actual time=647.901..758.829 rows=532473 loops=1 -> Parallel Seq Scan on public.customer (cost=0.00..3838.06 rows=84706 width=17) (actual time=0.009..6.282 rows=72000 loops=2) Output: customer.c_customer_sk, customer.c_customer_id, customer.c_current_cdemo_sk, customer.c_current_hdemo_sk, customer.c_current_addr_sk, customer.c_first_shipto_date_sk, customer.c_first_sales_date_sk, customer.c_salutation, customer.c_first_name, customer.c_last_name, customer.c_preferred_cust_flag, customer.c_birth_day, customer.c_birth_month, customer.c_birth_year, customer.c_birth_country, customer.c_login, customer.c_email_address, customer.c_last_review_date_sk Worker 0: actual time=0.009..6.398 rows=71611 loops=1 -> Parallel Hash (cost=140560.90..140560.90 rows=11398 width=8) (actual time=617.582..617.586 rows=546248 loops=2) Output: store_sales.ss_customer_sk, date_dim.d_date Buckets: 262144 (originally 32768) Batches: 8 (originally 1) Memory Usage: 7360kB Worker 0: actual time=616.651..616.654 rows=547248 loops=1 -> Parallel Hash Join (cost=2570.10..140560.90 rows=11398 width=8) (actual time=5.390..508.118 rows=546248 loops=2) Output: store_sales.ss_customer_sk, date_dim.d_date Inner Unique: true Hash Cond: (store_sales.ss_sold_date_sk = date_dim.d_date_sk) Worker 0: actual time=4.456..507.951 rows=547248 loops=1 -> Parallel Seq Scan on public.store_sales (cost=0.00..131691.82 rows=2399482 width=8) (actual time=0.018..260.792 rows=2879322 loops=2) Output: store_sales.ss_sold_date_sk, store_sales.ss_sold_time_sk, store_sales.ss_item_sk, store_sales.ss_customer_sk, store_sales.ss_cdemo_sk, store_sales.ss_hdemo_sk, store_sales.ss_addr_sk, store_sales.ss_store_sk, store_sales.ss_promo_sk, store_sales.ss_ticket_number, store_sales.ss_quantity, store_sales.ss_wholesale_cost, store_sales.ss_list_price, store_sales.ss_sales_price, store_sales.ss_ext_discount_amt, store_sales.ss_ext_sales_price, store_sales.ss_ext_wholesale_cost, store_sales.ss_ext_list_price, store_sales.ss_ext_tax, store_sales.ss_coupon_amt, store_sales.ss_net_paid, store_sales.ss_net_paid_inc_tax, store_sales.ss_net_profit Worker 0: actual time=0.020..261.441 rows=2887593 loops=1 -> Parallel Hash (cost=2567.55..2567.55 rows=204 width=8) (actual time=5.352..5.353 rows=182 loops=2) Output: date_dim.d_date, date_dim.d_date_sk Buckets: 1024 Batches: 1 Memory Usage: 72kB Worker 0: actual time=4.409..4.410 rows=152 loops=1 -> Parallel Seq Scan on public.date_dim (cost=0.00..2567.55 rows=204 width=8) (actual time=2.560..5.299 rows=182 loops=2) Output: date_dim.d_date, date_dim.d_date_sk Filter: ((date_dim.d_month_seq >= 1176) AND (date_dim.d_month_seq <= 1187)) Rows Removed by Filter: 36342 Worker 0: actual time=1.621..4.358 rows=152 loops=1 -> Subquery Scan on "*SELECT* 2" (cost=48503.68..49630.12 rows=8046 width=21) (actual time=700.050..828.388 rows=62744 loops=1) Output: "*SELECT* 2".c_last_name, "*SELECT* 2".c_first_name, "*SELECT* 2".d_date, 1 -> Unique (cost=48503.68..49549.66 rows=8046 width=17) (actual time=700.047..821.836 rows=62744 loops=1) Output: customer_1.c_last_name, customer_1.c_first_name, date_dim_1.d_date -> Gather Merge (cost=48503.68..49489.31 rows=8046 width=17) (actual time=700.047..814.136 rows=62744 loops=1) Output: customer_1.c_last_name, customer_1.c_first_name, date_dim_1.d_date Workers Planned: 1 Workers Launched: 1 -> Unique (cost=47503.67..47584.13 rows=8046 width=17) (actual time=666.348..763.403 rows=31372 loops=2) Output: customer_1.c_last_name, customer_1.c_first_name, date_dim_1.d_date Worker 0: actual time=632.937..724.548 rows=29426 loops=1 -> Sort (cost=47503.67..47523.78 rows=8046 width=17) (actual time=666.347..730.336 rows=284349 loops=2) Output: customer_1.c_last_name, customer_1.c_first_name, date_dim_1.d_date Sort Key: customer_1.c_last_name, customer_1.c_first_name, date_dim_1.d_date Sort Method: external merge Disk: 8576kB Worker 0: actual time=632.936..692.503 rows=266513 loops=1 Sort Method: external merge Disk: 7576kB -> Nested Loop (cost=0.85..46981.72 rows=8046 width=17) (actual time=1.852..454.111 rows=284349 loops=2) Output: customer_1.c_last_name, customer_1.c_first_name, date_dim_1.d_date Inner Unique: true Worker 0: actual time=0.681..433.435 rows=266513 loops=1 -> Nested Loop (cost=0.43..43423.72 rows=8046 width=8) (actual time=1.829..88.512 rows=285052 loops=2) Output: catalog_sales.cs_bill_customer_sk, date_dim_1.d_date Worker 0: actual time=0.648..85.747 rows=267146 loops=1 -> Parallel Seq Scan on public.date_dim date_dim_1 (cost=0.00..2567.55 rows=204 width=8) (actual time=1.789..3.883 rows=182 loops=2) Output: date_dim_1.d_date_sk, date_dim_1.d_date_id, date_dim_1.d_date, date_dim_1.d_month_seq, date_dim_1.d_week_seq, date_dim_1.d_quarter_seq, date_dim_1.d_year, date_dim_1.d_dow, date_dim_1.d_moy, date_dim_1.d_dom, date_dim_1.d_qoy, date_dim_1.d_fy_year, date_dim_1.d_fy_quarter_seq, date_dim_1.d_fy_week_seq, date_dim_1.d_day_name, date_dim_1.d_quarter_name, date_dim_1.d_holiday, date_dim_1.d_weekend, date_dim_1.d_following_holiday, date_dim_1.d_first_dom, date_dim_1.d_last_dom, date_dim_1.d_same_day_ly, date_dim_1.d_same_day_lq, date_dim_1.d_current_day, date_dim_1.d_current_week, date_dim_1.d_current_month, date_dim_1.d_current_quarter, date_dim_1.d_current_year Filter: ((date_dim_1.d_month_seq >= 1176) AND (date_dim_1.d_month_seq <= 1187)) Rows Removed by Filter: 36342 Worker 0: actual time=0.597..4.725 rows=172 loops=1 -> Index Scan using idx_cs_sold_date_sk on public.catalog_sales (cost=0.43..184.68 rows=1560 width=8) (actual time=0.004..0.317 rows=1562 loops=365) Output: catalog_sales.cs_sold_date_sk, catalog_sales.cs_sold_time_sk, catalog_sales.cs_ship_date_sk, catalog_sales.cs_bill_customer_sk, catalog_sales.cs_bill_cdemo_sk, catalog_sales.cs_bill_hdemo_sk, catalog_sales.cs_bill_addr_sk, catalog_sales.cs_ship_customer_sk, catalog_sales.cs_ship_cdemo_sk, catalog_sales.cs_ship_hdemo_sk, catalog_sales.cs_ship_addr_sk, catalog_sales.cs_call_center_sk, catalog_sales.cs_catalog_page_sk, catalog_sales.cs_ship_mode_sk, catalog_sales.cs_warehouse_sk, catalog_sales.cs_item_sk, catalog_sales.cs_promo_sk, catalog_sales.cs_order_number, catalog_sales.cs_quantity, catalog_sales.cs_wholesale_cost, catalog_sales.cs_list_price, catalog_sales.cs_sales_price, catalog_sales.cs_ext_discount_amt, catalog_sales.cs_ext_sales_price, catalog_sales.cs_ext_wholesale_cost, catalog_sales.cs_ext_list_price, catalog_sales.cs_ext_tax, catalog_sales.cs_coupon_amt, catalog_sales.cs_ext_ship_cost, catalog_sales.cs_net_paid, catalog_sales.cs_net_paid_inc_tax, catalog_sales.cs_net_paid_inc_ship, catalog_sales.cs_net_paid_inc_ship_tax, catalog_sales.cs_net_profit Index Cond: (catalog_sales.cs_sold_date_sk = date_dim_1.d_date_sk) Worker 0: actual time=0.004..0.323 rows=1553 loops=172 -> Index Scan using customer_pkey on public.customer customer_1 (cost=0.42..0.44 rows=1 width=17) (actual time=0.001..0.001 rows=1 loops=570105) Output: customer_1.c_customer_sk, customer_1.c_customer_id, customer_1.c_current_cdemo_sk, customer_1.c_current_hdemo_sk, customer_1.c_current_addr_sk, customer_1.c_first_shipto_date_sk, customer_1.c_first_sales_date_sk, customer_1.c_salutation, customer_1.c_first_name, customer_1.c_last_name, customer_1.c_preferred_cust_flag, customer_1.c_birth_day, customer_1.c_birth_month, customer_1.c_birth_year, customer_1.c_birth_country, customer_1.c_login, customer_1.c_email_address, customer_1.c_last_review_date_sk Index Cond: (customer_1.c_customer_sk = catalog_sales.cs_bill_customer_sk) Worker 0: actual time=0.001..0.001 rows=1 loops=267146 -> Subquery Scan on "*SELECT* 3" (cost=50541.84..51329.11 rows=5708 width=21) (actual time=302.042..336.820 rows=23737 loops=1) Output: "*SELECT* 3".c_last_name, "*SELECT* 3".c_first_name, "*SELECT* 3".d_date, 1 -> Unique (cost=50541.84..51272.03 rows=5708 width=17) (actual time=302.039..334.329 rows=23737 loops=1) Output: customer_2.c_last_name, customer_2.c_first_name, date_dim_2.d_date -> Gather Merge (cost=50541.84..51229.22 rows=5708 width=17) (actual time=302.039..331.296 rows=24128 loops=1) Output: customer_2.c_last_name, customer_2.c_first_name, date_dim_2.d_date Workers Planned: 2 Workers Launched: 2 -> Unique (cost=49541.81..49570.35 rows=2854 width=17) (actual time=298.771..320.560 rows=8043 loops=3) Output: customer_2.c_last_name, customer_2.c_first_name, date_dim_2.d_date Worker 0: actual time=297.820..319.743 rows=7997 loops=1 Worker 1: actual time=296.746..318.198 rows=7891 loops=1 -> Sort (cost=49541.81..49548.95 rows=2854 width=17) (actual time=298.770..309.603 rows=95920 loops=3) Output: customer_2.c_last_name, customer_2.c_first_name, date_dim_2.d_date Sort Key: customer_2.c_last_name, customer_2.c_first_name, date_dim_2.d_date Sort Method: external merge Disk: 2792kB Worker 0: actual time=297.818..308.737 rows=95675 loops=1 Sort Method: external merge Disk: 2720kB Worker 1: actual time=296.745..307.315 rows=93910 loops=1 Sort Method: external merge Disk: 2664kB -> Nested Loop (cost=2570.52..49378.01 rows=2854 width=17) (actual time=3.209..230.291 rows=95920 loops=3) Output: customer_2.c_last_name, customer_2.c_first_name, date_dim_2.d_date Inner Unique: true Worker 0: actual time=2.294..228.843 rows=95675 loops=1 Worker 1: actual time=2.670..230.164 rows=93910 loops=1 -> Parallel Hash Join (cost=2570.10..48102.56 rows=2854 width=8) (actual time=3.191..99.159 rows=95936 loops=3) Output: web_sales.ws_bill_customer_sk, date_dim_2.d_date Inner Unique: true Hash Cond: (web_sales.ws_sold_date_sk = date_dim_2.d_date_sk) Worker 0: actual time=2.270..97.201 rows=95690 loops=1 Worker 1: actual time=2.653..98.648 rows=93924 loops=1 -> Parallel Seq Scan on public.web_sales (cost=0.00..43955.21 rows=600821 width=8) (actual time=0.017..53.280 rows=480649 loops=3) Output: web_sales.ws_sold_date_sk, web_sales.ws_sold_time_sk, web_sales.ws_ship_date_sk, web_sales.ws_item_sk, web_sales.ws_bill_customer_sk, web_sales.ws_bill_cdemo_sk, web_sales.ws_bill_hdemo_sk, web_sales.ws_bill_addr_sk, web_sales.ws_ship_customer_sk, web_sales.ws_ship_cdemo_sk, web_sales.ws_ship_hdemo_sk, web_sales.ws_ship_addr_sk, web_sales.ws_web_page_sk, web_sales.ws_web_site_sk, web_sales.ws_ship_mode_sk, web_sales.ws_warehouse_sk, web_sales.ws_promo_sk, web_sales.ws_order_number, web_sales.ws_quantity, web_sales.ws_wholesale_cost, web_sales.ws_list_price, web_sales.ws_sales_price, web_sales.ws_ext_discount_amt, web_sales.ws_ext_sales_price, web_sales.ws_ext_wholesale_cost, web_sales.ws_ext_list_price, web_sales.ws_ext_tax, web_sales.ws_coupon_amt, web_sales.ws_ext_ship_cost, web_sales.ws_net_paid, web_sales.ws_net_paid_inc_tax, web_sales.ws_net_paid_inc_ship, web_sales.ws_net_paid_inc_ship_tax, web_sales.ws_net_profit Worker 0: actual time=0.023..52.210 rows=480453 loops=1 Worker 1: actual time=0.020..54.103 rows=473734 loops=1 -> Parallel Hash (cost=2567.55..2567.55 rows=204 width=8) (actual time=3.032..3.032 rows=122 loops=3) Output: date_dim_2.d_date, date_dim_2.d_date_sk Buckets: 1024 Batches: 1 Memory Usage: 104kB Worker 0: actual time=2.170..2.171 rows=76 loops=1 Worker 1: actual time=2.559..2.560 rows=96 loops=1 -> Parallel Seq Scan on public.date_dim date_dim_2 (cost=0.00..2567.55 rows=204 width=8) (actual time=1.329..2.976 rows=122 loops=3) Output: date_dim_2.d_date, date_dim_2.d_date_sk Filter: ((date_dim_2.d_month_seq >= 1176) AND (date_dim_2.d_month_seq <= 1187)) Rows Removed by Filter: 24228 Worker 0: actual time=0.464..2.104 rows=76 loops=1 Worker 1: actual time=0.857..2.506 rows=96 loops=1 -> Index Scan using customer_pkey on public.customer customer_2 (cost=0.42..0.45 rows=1 width=17) (actual time=0.001..0.001 rows=1 loops=287809) Output: customer_2.c_customer_sk, customer_2.c_customer_id, customer_2.c_current_cdemo_sk, customer_2.c_current_hdemo_sk, customer_2.c_current_addr_sk, customer_2.c_first_shipto_date_sk, customer_2.c_first_sales_date_sk, customer_2.c_salutation, customer_2.c_first_name, customer_2.c_last_name, customer_2.c_preferred_cust_flag, customer_2.c_birth_day, customer_2.c_birth_month, customer_2.c_birth_year, customer_2.c_birth_country, customer_2.c_login, customer_2.c_email_address, customer_2.c_last_review_date_sk Index Cond: (customer_2.c_customer_sk = web_sales.ws_bill_customer_sk) Worker 0: actual time=0.001..0.001 rows=1 loops=95690 Worker 1: actual time=0.001..0.001 rows=1 loops=93924 Planning Time: 2.926 ms Execution Time: 2642.408 ms (147 rows) [text/plain] tpcds-q87-e5b8a4c09-plan.txt (25.5K, ../../[email protected]/3-tpcds-q87-e5b8a4c09-plan.txt) download | inline: QUERY PLAN -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Aggregate (cost=254949.93..254949.94 rows=1 width=8) (actual time=2242.324..2259.657 rows=1 loops=1) Output: count(*) -> Subquery Scan on cool_cust (cost=149622.00..254907.85 rows=16834 width=0) (actual time=2226.567..2256.123 rows=93140 loops=1) Output: cool_cust.c_last_name, cool_cust.c_first_name, cool_cust.d_date -> HashSetOp Except (cost=149622.00..254739.51 rows=16834 width=144) (actual time=2226.566..2247.422 rows=93140 loops=1) Output: "*SELECT* 1".c_last_name, "*SELECT* 1".c_first_name, "*SELECT* 1".d_date, (0) -> Append (cost=149622.00..254559.51 rows=23999 width=144) (actual time=1771.245..2221.971 rows=117004 loops=1) -> Result (cost=149622.00..202870.82 rows=16834 width=144) (actual time=1771.245..1810.319 rows=93267 loops=1) Output: "*SELECT* 1".c_last_name, "*SELECT* 1".c_first_name, "*SELECT* 1".d_date, 0 -> HashSetOp Except (cost=149622.00..202702.48 rows=16834 width=144) (actual time=1771.244..1800.321 rows=93267 loops=1) Output: "*SELECT* 1".c_last_name, "*SELECT* 1".c_first_name, "*SELECT* 1".d_date, (0) -> Append (cost=149622.00..202513.10 rows=25251 width=144) (actual time=1018.607..1759.828 rows=156635 loops=1) -> Subquery Scan on "*SELECT* 1" (cost=149622.00..149958.68 rows=16834 width=21) (actual time=1018.606..1074.468 rows=93891 loops=1) Output: "*SELECT* 1".c_last_name, "*SELECT* 1".c_first_name, "*SELECT* 1".d_date, 0 -> Unique (cost=149622.00..149790.34 rows=16834 width=17) (actual time=1018.604..1064.790 rows=93891 loops=1) Output: customer.c_last_name, customer.c_first_name, date_dim.d_date -> Sort (cost=149622.00..149664.09 rows=16834 width=17) (actual time=1018.603..1052.591 rows=94199 loops=1) Output: customer.c_last_name, customer.c_first_name, date_dim.d_date Sort Key: customer.c_last_name, customer.c_first_name, date_dim.d_date Sort Method: external merge Disk: 2696kB -> Gather (cost=146588.59..148440.33 rows=16834 width=17) (actual time=880.899..913.978 rows=94199 loops=1) Output: customer.c_last_name, customer.c_first_name, date_dim.d_date Workers Planned: 1 Workers Launched: 1 -> HashAggregate (cost=145588.59..145756.93 rows=16834 width=17) (actual time=879.119..887.352 rows=47100 loops=2) Output: customer.c_last_name, customer.c_first_name, date_dim.d_date Group Key: customer.c_last_name, customer.c_first_name, date_dim.d_date Batches: 1 Memory Usage: 4625kB Worker 0: actual time=877.609..886.041 rows=44455 loops=1 Batches: 1 Memory Usage: 4369kB -> Parallel Hash Join (cost=140710.34..145462.34 rows=16834 width=17) (actual time=644.707..767.476 rows=533434 loops=2) Output: customer.c_last_name, customer.c_first_name, date_dim.d_date Hash Cond: (customer.c_customer_sk = store_sales.ss_customer_sk) Worker 0: actual time=650.882..772.536 rows=503226 loops=1 -> Parallel Seq Scan on public.customer (cost=0.00..3838.06 rows=84706 width=17) (actual time=0.009..6.004 rows=72000 loops=2) Output: customer.c_customer_sk, customer.c_customer_id, customer.c_current_cdemo_sk, customer.c_current_hdemo_sk, customer.c_current_addr_sk, customer.c_first_shipto_date_sk, customer.c_first_sales_date_sk, customer.c_salutation, customer.c_first_name, customer.c_last_name, customer.c_preferred_cust_flag, customer.c_birth_day, customer.c_birth_month, customer.c_birth_year, customer.c_birth_country, customer.c_login, customer.c_email_address, customer.c_last_review_date_sk Worker 0: actual time=0.009..6.171 rows=70942 loops=1 -> Parallel Hash (cost=140561.29..140561.29 rows=11924 width=8) (actual time=615.499..615.504 rows=546248 loops=2) Output: store_sales.ss_customer_sk, date_dim.d_date Buckets: 262144 (originally 32768) Batches: 8 (originally 1) Memory Usage: 7360kB Worker 0: actual time=614.329..614.333 rows=542130 loops=1 -> Parallel Hash Join (cost=2570.23..140561.29 rows=11924 width=8) (actual time=5.364..506.303 rows=546248 loops=2) Output: store_sales.ss_customer_sk, date_dim.d_date Inner Unique: true Hash Cond: (store_sales.ss_sold_date_sk = date_dim.d_date_sk) Worker 0: actual time=4.176..503.249 rows=542130 loops=1 -> Parallel Seq Scan on public.store_sales (cost=0.00..131692.02 rows=2399502 width=8) (actual time=0.017..258.551 rows=2879322 loops=2) Output: store_sales.ss_sold_date_sk, store_sales.ss_sold_time_sk, store_sales.ss_item_sk, store_sales.ss_customer_sk, store_sales.ss_cdemo_sk, store_sales.ss_hdemo_sk, store_sales.ss_addr_sk, store_sales.ss_store_sk, store_sales.ss_promo_sk, store_sales.ss_ticket_number, store_sales.ss_quantity, store_sales.ss_wholesale_cost, store_sales.ss_list_price, store_sales.ss_sales_price, store_sales.ss_ext_discount_amt, store_sales.ss_ext_sales_price, store_sales.ss_ext_wholesale_cost, store_sales.ss_ext_list_price, store_sales.ss_ext_tax, store_sales.ss_coupon_amt, store_sales.ss_net_paid, store_sales.ss_net_paid_inc_tax, store_sales.ss_net_profit Worker 0: actual time=0.021..258.112 rows=2861258 loops=1 -> Parallel Hash (cost=2567.55..2567.55 rows=214 width=8) (actual time=5.302..5.303 rows=182 loops=2) Output: date_dim.d_date, date_dim.d_date_sk Buckets: 1024 Batches: 1 Memory Usage: 72kB Worker 0: actual time=4.121..4.122 rows=172 loops=1 -> Parallel Seq Scan on public.date_dim (cost=0.00..2567.55 rows=214 width=8) (actual time=2.697..5.256 rows=182 loops=2) Output: date_dim.d_date, date_dim.d_date_sk Filter: ((date_dim.d_month_seq >= 1176) AND (date_dim.d_month_seq <= 1187)) Rows Removed by Filter: 36342 Worker 0: actual time=1.520..4.075 rows=172 loops=1 -> Subquery Scan on "*SELECT* 2" (cost=52259.82..52428.16 rows=8417 width=21) (actual time=653.640..676.879 rows=62744 loops=1) Output: "*SELECT* 2".c_last_name, "*SELECT* 2".c_first_name, "*SELECT* 2".d_date, 1 -> Unique (cost=52259.82..52343.99 rows=8417 width=17) (actual time=653.639..670.405 rows=62744 loops=1) Output: customer_1.c_last_name, customer_1.c_first_name, date_dim_1.d_date -> Sort (cost=52259.82..52280.86 rows=8417 width=17) (actual time=653.637..662.428 rows=62744 loops=1) Output: customer_1.c_last_name, customer_1.c_first_name, date_dim_1.d_date Sort Key: customer_1.c_last_name, customer_1.c_first_name, date_dim_1.d_date Sort Method: external merge Disk: 1800kB -> Gather (cost=50785.20..51711.07 rows=8417 width=17) (actual time=562.158..571.737 rows=62744 loops=1) Output: customer_1.c_last_name, customer_1.c_first_name, date_dim_1.d_date Workers Planned: 1 Workers Launched: 1 -> HashAggregate (cost=49785.20..49869.37 rows=8417 width=17) (actual time=538.263..544.336 rows=31372 loops=2) Output: customer_1.c_last_name, customer_1.c_first_name, date_dim_1.d_date Group Key: customer_1.c_last_name, customer_1.c_first_name, date_dim_1.d_date Batches: 1 Memory Usage: 3601kB Worker 0: actual time=514.615..520.864 rows=29639 loops=1 Batches: 1 Memory Usage: 3601kB -> Nested Loop (cost=0.85..49722.07 rows=8417 width=17) (actual time=2.049..469.747 rows=284349 loops=2) Output: customer_1.c_last_name, customer_1.c_first_name, date_dim_1.d_date Inner Unique: true Worker 0: actual time=0.961..449.691 rows=268480 loops=1 -> Nested Loop (cost=0.43..46000.01 rows=8417 width=8) (actual time=2.029..93.688 rows=285052 loops=2) Output: catalog_sales.cs_bill_customer_sk, date_dim_1.d_date Worker 0: actual time=0.932..90.920 rows=269122 loops=1 -> Parallel Seq Scan on public.date_dim date_dim_1 (cost=0.00..2567.55 rows=214 width=8) (actual time=1.983..4.233 rows=182 loops=2) Output: date_dim_1.d_date_sk, date_dim_1.d_date_id, date_dim_1.d_date, date_dim_1.d_month_seq, date_dim_1.d_week_seq, date_dim_1.d_quarter_seq, date_dim_1.d_year, date_dim_1.d_dow, date_dim_1.d_moy, date_dim_1.d_dom, date_dim_1.d_qoy, date_dim_1.d_fy_year, date_dim_1.d_fy_quarter_seq, date_dim_1.d_fy_week_seq, date_dim_1.d_day_name, date_dim_1.d_quarter_name, date_dim_1.d_holiday, date_dim_1.d_weekend, date_dim_1.d_following_holiday, date_dim_1.d_first_dom, date_dim_1.d_last_dom, date_dim_1.d_same_day_ly, date_dim_1.d_same_day_lq, date_dim_1.d_current_day, date_dim_1.d_current_week, date_dim_1.d_current_month, date_dim_1.d_current_quarter, date_dim_1.d_current_year Filter: ((date_dim_1.d_month_seq >= 1176) AND (date_dim_1.d_month_seq <= 1187)) Rows Removed by Filter: 36342 Worker 0: actual time=0.886..5.324 rows=175 loops=1 -> Index Scan using idx_cs_sold_date_sk on public.catalog_sales (cost=0.43..187.36 rows=1560 width=8) (actual time=0.004..0.341 rows=1562 loops=365) Output: catalog_sales.cs_sold_date_sk, catalog_sales.cs_sold_time_sk, catalog_sales.cs_ship_date_sk, catalog_sales.cs_bill_customer_sk, catalog_sales.cs_bill_cdemo_sk, catalog_sales.cs_bill_hdemo_sk, catalog_sales.cs_bill_addr_sk, catalog_sales.cs_ship_customer_sk, catalog_sales.cs_ship_cdemo_sk, catalog_sales.cs_ship_hdemo_sk, catalog_sales.cs_ship_addr_sk, catalog_sales.cs_call_center_sk, catalog_sales.cs_catalog_page_sk, catalog_sales.cs_ship_mode_sk, catalog_sales.cs_warehouse_sk, catalog_sales.cs_item_sk, catalog_sales.cs_promo_sk, catalog_sales.cs_order_number, catalog_sales.cs_quantity, catalog_sales.cs_wholesale_cost, catalog_sales.cs_list_price, catalog_sales.cs_sales_price, catalog_sales.cs_ext_discount_amt, catalog_sales.cs_ext_sales_price, catalog_sales.cs_ext_wholesale_cost, catalog_sales.cs_ext_list_price, catalog_sales.cs_ext_tax, catalog_sales.cs_coupon_amt, catalog_sales.cs_ext_ship_cost, catalog_sales.cs_net_paid, catalog_sales.cs_net_paid_inc_tax, catalog_sales.cs_net_paid_inc_ship, catalog_sales.cs_net_paid_inc_ship_tax, catalog_sales.cs_net_profit Index Cond: (catalog_sales.cs_sold_date_sk = date_dim_1.d_date_sk) Worker 0: actual time=0.005..0.342 rows=1538 loops=175 -> Index Scan using customer_pkey on public.customer customer_1 (cost=0.42..0.44 rows=1 width=17) (actual time=0.001..0.001 rows=1 loops=570105) Output: customer_1.c_customer_sk, customer_1.c_customer_id, customer_1.c_current_cdemo_sk, customer_1.c_current_hdemo_sk, customer_1.c_current_addr_sk, customer_1.c_first_shipto_date_sk, customer_1.c_first_sales_date_sk, customer_1.c_salutation, customer_1.c_first_name, customer_1.c_last_name, customer_1.c_preferred_cust_flag, customer_1.c_birth_day, customer_1.c_birth_month, customer_1.c_birth_year, customer_1.c_birth_country, customer_1.c_login, customer_1.c_email_address, customer_1.c_last_review_date_sk Index Cond: (customer_1.c_customer_sk = catalog_sales.cs_bill_customer_sk) Worker 0: actual time=0.001..0.001 rows=1 loops=269122 -> Subquery Scan on "*SELECT* 3" (cost=50608.83..51568.70 rows=7165 width=21) (actual time=302.571..405.305 rows=23737 loops=1) Output: "*SELECT* 3".c_last_name, "*SELECT* 3".c_first_name, "*SELECT* 3".d_date, 1 -> Unique (cost=50608.83..51497.05 rows=7165 width=17) (actual time=302.568..402.818 rows=23737 loops=1) Output: customer_2.c_last_name, customer_2.c_first_name, date_dim_2.d_date -> Gather Merge (cost=50608.83..51443.31 rows=7165 width=17) (actual time=302.567..372.246 rows=287761 loops=1) Output: customer_2.c_last_name, customer_2.c_first_name, date_dim_2.d_date Workers Planned: 2 Workers Launched: 2 -> Sort (cost=49608.81..49616.27 rows=2985 width=17) (actual time=298.204..310.075 rows=95920 loops=3) Output: customer_2.c_last_name, customer_2.c_first_name, date_dim_2.d_date Sort Key: customer_2.c_last_name, customer_2.c_first_name, date_dim_2.d_date Sort Method: external merge Disk: 2824kB Worker 0: actual time=297.401..308.205 rows=96226 loops=1 Sort Method: external merge Disk: 2736kB Worker 1: actual time=294.914..308.683 rows=92128 loops=1 Sort Method: external merge Disk: 2624kB -> Nested Loop (cost=2570.65..49436.52 rows=2985 width=17) (actual time=3.205..229.192 rows=95920 loops=3) Output: customer_2.c_last_name, customer_2.c_first_name, date_dim_2.d_date Inner Unique: true Worker 0: actual time=2.263..227.978 rows=96226 loops=1 Worker 1: actual time=2.608..228.161 rows=92128 loops=1 -> Parallel Hash Join (cost=2570.23..48102.52 rows=2985 width=8) (actual time=3.186..100.585 rows=95936 loops=3) Output: web_sales.ws_bill_customer_sk, date_dim_2.d_date Inner Unique: true Hash Cond: (web_sales.ws_sold_date_sk = date_dim_2.d_date_sk) Worker 0: actual time=2.240..100.451 rows=96240 loops=1 Worker 1: actual time=2.587..98.637 rows=92152 loops=1 -> Parallel Seq Scan on public.web_sales (cost=0.00..43955.08 rows=600808 width=8) (actual time=0.017..53.842 rows=480649 loops=3) Output: web_sales.ws_sold_date_sk, web_sales.ws_sold_time_sk, web_sales.ws_ship_date_sk, web_sales.ws_item_sk, web_sales.ws_bill_customer_sk, web_sales.ws_bill_cdemo_sk, web_sales.ws_bill_hdemo_sk, web_sales.ws_bill_addr_sk, web_sales.ws_ship_customer_sk, web_sales.ws_ship_cdemo_sk, web_sales.ws_ship_hdemo_sk, web_sales.ws_ship_addr_sk, web_sales.ws_web_page_sk, web_sales.ws_web_site_sk, web_sales.ws_ship_mode_sk, web_sales.ws_warehouse_sk, web_sales.ws_promo_sk, web_sales.ws_order_number, web_sales.ws_quantity, web_sales.ws_wholesale_cost, web_sales.ws_list_price, web_sales.ws_sales_price, web_sales.ws_ext_discount_amt, web_sales.ws_ext_sales_price, web_sales.ws_ext_wholesale_cost, web_sales.ws_ext_list_price, web_sales.ws_ext_tax, web_sales.ws_coupon_amt, web_sales.ws_ext_ship_cost, web_sales.ws_net_paid, web_sales.ws_net_paid_inc_tax, web_sales.ws_net_paid_inc_ship, web_sales.ws_net_paid_inc_ship_tax, web_sales.ws_net_profit Worker 0: actual time=0.021..53.860 rows=486377 loops=1 Worker 1: actual time=0.022..54.070 rows=460098 loops=1 -> Parallel Hash (cost=2567.55..2567.55 rows=214 width=8) (actual time=3.007..3.008 rows=122 loops=3) Output: date_dim_2.d_date, date_dim_2.d_date_sk Buckets: 1024 Batches: 1 Memory Usage: 104kB Worker 0: actual time=2.123..2.123 rows=76 loops=1 Worker 1: actual time=2.433..2.434 rows=114 loops=1 -> Parallel Seq Scan on public.date_dim date_dim_2 (cost=0.00..2567.55 rows=214 width=8) (actual time=1.326..2.956 rows=122 loops=3) Output: date_dim_2.d_date, date_dim_2.d_date_sk Filter: ((date_dim_2.d_month_seq >= 1176) AND (date_dim_2.d_month_seq <= 1187)) Rows Removed by Filter: 24228 Worker 0: actual time=0.438..2.064 rows=76 loops=1 Worker 1: actual time=0.751..2.375 rows=114 loops=1 -> Index Scan using customer_pkey on public.customer customer_2 (cost=0.42..0.45 rows=1 width=17) (actual time=0.001..0.001 rows=1 loops=287809) Output: customer_2.c_customer_sk, customer_2.c_customer_id, customer_2.c_current_cdemo_sk, customer_2.c_current_hdemo_sk, customer_2.c_current_addr_sk, customer_2.c_first_shipto_date_sk, customer_2.c_first_sales_date_sk, customer_2.c_salutation, customer_2.c_first_name, customer_2.c_last_name, customer_2.c_preferred_cust_flag, customer_2.c_birth_day, customer_2.c_birth_month, customer_2.c_birth_year, customer_2.c_birth_country, customer_2.c_login, customer_2.c_email_address, customer_2.c_last_review_date_sk Index Cond: (customer_2.c_customer_sk = web_sales.ws_bill_customer_sk) Worker 0: actual time=0.001..0.001 rows=1 loops=96240 Worker 1: actual time=0.001..0.001 rows=1 loops=92152 Planning Time: 3.040 ms Execution Time: 2262.778 ms (145 rows) ^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: benchmark results comparing versions 15.2 and 16 @ 2023-05-10 22:27 David Rowley <[email protected]> parent: Alexander Lakhin <[email protected]> 0 siblings, 1 reply; 8+ messages in thread From: David Rowley @ 2023-05-10 22:27 UTC (permalink / raw) To: Alexander Lakhin <[email protected]>; +Cc: MARK CALLAGHAN <[email protected]>; [email protected]; Andres Freund <[email protected]> On Thu, 11 May 2023 at 01:00, Alexander Lakhin <[email protected]> wrote: > This time `git bisect` pointed at 3c6fc5820. Having compared execution plans > (both attached), I see the following differences (3c6fc5820~1 vs 3c6fc5820): Based on what you've sent, I'm uninspired to want to try to do anything about it. The patched version finds a plan that's cheaper. The row estimates are miles off with both plans. I'm not sure what we're supposed to do here. It's pretty hard to make changes to the planner's path generation without risking that a bad plan is chosen when it wasn't beforehand with bad row estimates. Is the new plan still slower if you increase work_mem so that the sort no longer goes to disk? Maybe the planner would have picked Hash Aggregate if the row estimates had been such that cost_tuplesort() knew that the sort would have gone to disk. David ^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: benchmark results comparing versions 15.2 and 16 @ 2023-05-11 13:00 Alexander Lakhin <[email protected]> parent: David Rowley <[email protected]> 0 siblings, 1 reply; 8+ messages in thread From: Alexander Lakhin @ 2023-05-11 13:00 UTC (permalink / raw) To: David Rowley <[email protected]>; +Cc: MARK CALLAGHAN <[email protected]>; [email protected]; Andres Freund <[email protected]> 11.05.2023 01:27, David Rowley wrote: > On Thu, 11 May 2023 at 01:00, Alexander Lakhin <[email protected]> wrote: >> This time `git bisect` pointed at 3c6fc5820. Having compared execution plans >> (both attached), I see the following differences (3c6fc5820~1 vs 3c6fc5820): > Based on what you've sent, I'm uninspired to want to try to do > anything about it. The patched version finds a plan that's cheaper. > The row estimates are miles off with both plans. I've made sure that s64da-benchmark performs analyze before running the queries (pg_class.reltuples fields for tables in question contain actual counts), so it seems that nothing can be done on the benchmark side to improve those estimates. > ... It's pretty hard to make changes to the > planner's path generation without risking that a bad plan is chosen > when it wasn't beforehand with bad row estimates. Yeah, I see. It's also interesting to me, which tests perform better after that commit. It takes several hours to run all tests, so I can't present results quickly, but I'll try to collect this information next week. > Is the new plan still slower if you increase work_mem so that the sort > no longer goes to disk? Maybe the planner would have picked Hash > Aggregate if the row estimates had been such that cost_tuplesort() > knew that the sort would have gone to disk. Yes, increasing work_mem to 50MB doesn't affect the plans (new plans attached), though the sort method changed to quicksort. The former plan is still executed slightly faster. Best regards, Alexander QUERY PLAN -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Aggregate (cost=254862.89..254862.90 rows=1 width=8) (actual time=2567.064..2581.619 rows=1 loops=1) Output: count(*) -> Subquery Scan on cool_cust (cost=147645.17..254820.81 rows=16834 width=0) (actual time=2551.091..2578.005 rows=93140 loops=1) Output: cool_cust.c_last_name, cool_cust.c_first_name, cool_cust.d_date -> HashSetOp Except (cost=147645.17..254652.47 rows=16834 width=144) (actual time=2551.090..2569.217 rows=93140 loops=1) Output: "*SELECT* 1".c_last_name, "*SELECT* 1".c_first_name, "*SELECT* 1".d_date, (0) -> Append (cost=147645.17..254481.44 rows=22804 width=144) (actual time=2162.417..2542.403 rows=117004 loops=1) -> Result (cost=147645.17..202935.12 rows=16834 width=144) (actual time=2162.416..2200.221 rows=93267 loops=1) Output: "*SELECT* 1".c_last_name, "*SELECT* 1".c_first_name, "*SELECT* 1".d_date, 0 -> HashSetOp Except (cost=147645.17..202766.78 rows=16834 width=144) (actual time=2162.414..2190.284 rows=93267 loops=1) Output: "*SELECT* 1".c_last_name, "*SELECT* 1".c_first_name, "*SELECT* 1".d_date, (0) -> Append (cost=147645.17..202577.39 rows=25251 width=144) (actual time=1175.080..2146.489 rows=156635 loops=1) -> Subquery Scan on "*SELECT* 1" (cost=147645.17..150001.93 rows=16834 width=21) (actual time=1175.079..1315.210 rows=93891 loops=1) Output: "*SELECT* 1".c_last_name, "*SELECT* 1".c_first_name, "*SELECT* 1".d_date, 0 -> Unique (cost=147645.17..149833.59 rows=16834 width=17) (actual time=1175.075..1305.410 rows=93891 loops=1) Output: customer.c_last_name, customer.c_first_name, date_dim.d_date -> Gather Merge (cost=147645.17..149707.33 rows=16834 width=17) (actual time=1175.075..1293.810 rows=94207 loops=1) Output: customer.c_last_name, customer.c_first_name, date_dim.d_date Workers Planned: 1 Workers Launched: 1 -> Unique (cost=146645.16..146813.50 rows=16834 width=17) (actual time=1171.845..1259.795 rows=47104 loops=2) Output: customer.c_last_name, customer.c_first_name, date_dim.d_date Worker 0: actual time=1168.901..1257.819 rows=47011 loops=1 -> Sort (cost=146645.16..146687.24 rows=16834 width=17) (actual time=1171.842..1199.145 rows=533434 loops=2) Output: customer.c_last_name, customer.c_first_name, date_dim.d_date Sort Key: customer.c_last_name, customer.c_first_name, date_dim.d_date Sort Method: quicksort Memory: 41416kB Worker 0: actual time=1168.898..1196.043 rows=533101 loops=1 Sort Method: quicksort Memory: 41393kB -> Parallel Hash Join (cost=140711.42..145463.49 rows=16834 width=17) (actual time=612.177..710.988 rows=533434 loops=2) Output: customer.c_last_name, customer.c_first_name, date_dim.d_date Hash Cond: (customer.c_customer_sk = store_sales.ss_customer_sk) Worker 0: actual time=611.023..709.758 rows=533101 loops=1 -> Parallel Seq Scan on public.customer (cost=0.00..3838.06 rows=84706 width=17) (actual time=0.007..6.935 rows=72000 loops=2) Output: customer.c_customer_sk, customer.c_customer_id, customer.c_current_cdemo_sk, customer.c_current_hdemo_sk, customer.c_current_addr_sk, customer.c_first_shipto_date_sk, customer.c_first_sales_date_sk, customer.c_salutation, customer.c_first_name, customer.c_last_name, customer.c_preferred_cust_flag, customer.c_birth_day, customer.c_birth_month, customer.c_birth_year, customer.c_birth_country, customer.c_login, customer.c_email_address, customer.c_last_review_date_sk Worker 0: actual time=0.012..6.906 rows=72071 loops=1 -> Parallel Hash (cost=140562.37..140562.37 rows=11924 width=8) (actual time=611.999..612.003 rows=546248 loops=2) Output: store_sales.ss_customer_sk, date_dim.d_date Buckets: 2097152 (originally 32768) Batches: 1 (originally 1) Memory Usage: 74272kB Worker 0: actual time=610.930..610.934 rows=548215 loops=1 -> Parallel Hash Join (cost=2570.23..140562.37 rows=11924 width=8) (actual time=5.025..509.830 rows=546248 loops=2) Output: store_sales.ss_customer_sk, date_dim.d_date Inner Unique: true Hash Cond: (store_sales.ss_sold_date_sk = date_dim.d_date_sk) Worker 0: actual time=3.992..508.085 rows=548215 loops=1 -> Parallel Seq Scan on public.store_sales (cost=0.00..131692.88 rows=2399588 width=8) (actual time=0.016..263.040 rows=2879322 loops=2) Output: store_sales.ss_sold_date_sk, store_sales.ss_sold_time_sk, store_sales.ss_item_sk, store_sales.ss_customer_sk, store_sales.ss_cdemo_sk, store_sales.ss_hdemo_sk, store_sales.ss_addr_sk, store_sales.ss_store_sk, store_sales.ss_promo_sk, store_sales.ss_ticket_number, store_sales.ss_quantity, store_sales.ss_wholesale_cost, store_sales.ss_list_price, store_sales.ss_sales_price, store_sales.ss_ext_discount_amt, store_sales.ss_ext_sales_price, store_sales.ss_ext_wholesale_cost, store_sales.ss_ext_list_price, store_sales.ss_ext_tax, store_sales.ss_coupon_amt, store_sales.ss_net_paid, store_sales.ss_net_paid_inc_tax, store_sales.ss_net_profit Worker 0: actual time=0.018..262.235 rows=2882396 loops=1 -> Parallel Hash (cost=2567.55..2567.55 rows=214 width=8) (actual time=4.951..4.953 rows=182 loops=2) Output: date_dim.d_date, date_dim.d_date_sk Buckets: 1024 Batches: 1 Memory Usage: 72kB Worker 0: actual time=3.877..3.879 rows=137 loops=1 -> Parallel Seq Scan on public.date_dim (cost=0.00..2567.55 rows=214 width=8) (actual time=2.164..4.905 rows=182 loops=2) Output: date_dim.d_date, date_dim.d_date_sk Filter: ((date_dim.d_month_seq >= 1176) AND (date_dim.d_month_seq <= 1187)) Rows Removed by Filter: 36342 Worker 0: actual time=1.096..3.834 rows=137 loops=1 -> Subquery Scan on "*SELECT* 2" (cost=51270.83..52449.21 rows=8417 width=21) (actual time=750.303..822.786 rows=62744 loops=1) Output: "*SELECT* 2".c_last_name, "*SELECT* 2".c_first_name, "*SELECT* 2".d_date, 1 -> Unique (cost=51270.83..52365.04 rows=8417 width=17) (actual time=750.300..816.251 rows=62744 loops=1) Output: customer_1.c_last_name, customer_1.c_first_name, date_dim_1.d_date -> Gather Merge (cost=51270.83..52301.92 rows=8417 width=17) (actual time=750.299..808.594 rows=62744 loops=1) Output: customer_1.c_last_name, customer_1.c_first_name, date_dim_1.d_date Workers Planned: 1 Workers Launched: 1 -> Unique (cost=50270.82..50354.99 rows=8417 width=17) (actual time=720.125..765.136 rows=31372 loops=2) Output: customer_1.c_last_name, customer_1.c_first_name, date_dim_1.d_date Worker 0: actual time=690.255..733.699 rows=29639 loops=1 -> Sort (cost=50270.82..50291.87 rows=8417 width=17) (actual time=720.123..732.766 rows=284349 loops=2) Output: customer_1.c_last_name, customer_1.c_first_name, date_dim_1.d_date Sort Key: customer_1.c_last_name, customer_1.c_first_name, date_dim_1.d_date Sort Method: quicksort Memory: 25655kB Worker 0: actual time=690.253..702.224 rows=268480 loops=1 Sort Method: quicksort Memory: 24250kB -> Nested Loop (cost=0.85..49722.07 rows=8417 width=17) (actual time=1.831..466.098 rows=284349 loops=2) Output: customer_1.c_last_name, customer_1.c_first_name, date_dim_1.d_date Inner Unique: true Worker 0: actual time=0.526..449.880 rows=268480 loops=1 -> Nested Loop (cost=0.43..46000.01 rows=8417 width=8) (actual time=1.811..91.963 rows=285052 loops=2) Output: catalog_sales.cs_bill_customer_sk, date_dim_1.d_date Worker 0: actual time=0.500..90.643 rows=269122 loops=1 -> Parallel Seq Scan on public.date_dim date_dim_1 (cost=0.00..2567.55 rows=214 width=8) (actual time=1.774..4.005 rows=182 loops=2) Output: date_dim_1.d_date_sk, date_dim_1.d_date_id, date_dim_1.d_date, date_dim_1.d_month_seq, date_dim_1.d_week_seq, date_dim_1.d_quarter_seq, date_dim_1.d_year, date_dim_1.d_dow, date_dim_1.d_moy, date_dim_1.d_dom, date_dim_1.d_qoy, date_dim_1.d_fy_year, date_dim_1.d_fy_quarter_seq, date_dim_1.d_fy_week_seq, date_dim_1.d_day_name, date_dim_1.d_quarter_name, date_dim_1.d_holiday, date_dim_1.d_weekend, date_dim_1.d_following_holiday, date_dim_1.d_first_dom, date_dim_1.d_last_dom, date_dim_1.d_same_day_ly, date_dim_1.d_same_day_lq, date_dim_1.d_current_day, date_dim_1.d_current_week, date_dim_1.d_current_month, date_dim_1.d_current_quarter, date_dim_1.d_current_year Filter: ((date_dim_1.d_month_seq >= 1176) AND (date_dim_1.d_month_seq <= 1187)) Rows Removed by Filter: 36342 Worker 0: actual time=0.461..4.861 rows=175 loops=1 -> Index Scan using idx_cs_sold_date_sk on public.catalog_sales (cost=0.43..187.36 rows=1560 width=8) (actual time=0.004..0.328 rows=1562 loops=365) Output: catalog_sales.cs_sold_date_sk, catalog_sales.cs_sold_time_sk, catalog_sales.cs_ship_date_sk, catalog_sales.cs_bill_customer_sk, catalog_sales.cs_bill_cdemo_sk, catalog_sales.cs_bill_hdemo_sk, catalog_sales.cs_bill_addr_sk, catalog_sales.cs_ship_customer_sk, catalog_sales.cs_ship_cdemo_sk, catalog_sales.cs_ship_hdemo_sk, catalog_sales.cs_ship_addr_sk, catalog_sales.cs_call_center_sk, catalog_sales.cs_catalog_page_sk, catalog_sales.cs_ship_mode_sk, catalog_sales.cs_warehouse_sk, catalog_sales.cs_item_sk, catalog_sales.cs_promo_sk, catalog_sales.cs_order_number, catalog_sales.cs_quantity, catalog_sales.cs_wholesale_cost, catalog_sales.cs_list_price, catalog_sales.cs_sales_price, catalog_sales.cs_ext_discount_amt, catalog_sales.cs_ext_sales_price, catalog_sales.cs_ext_wholesale_cost, catalog_sales.cs_ext_list_price, catalog_sales.cs_ext_tax, catalog_sales.cs_coupon_amt, catalog_sales.cs_ext_ship_cost, catalog_sales.cs_net_paid, catalog_sales.cs_net_paid_inc_tax, catalog_sales.cs_net_paid_inc_ship, catalog_sales.cs_net_paid_inc_ship_tax, catalog_sales.cs_net_profit Index Cond: (catalog_sales.cs_sold_date_sk = date_dim_1.d_date_sk) Worker 0: actual time=0.004..0.333 rows=1538 loops=175 -> Index Scan using customer_pkey on public.customer customer_1 (cost=0.42..0.44 rows=1 width=17) (actual time=0.001..0.001 rows=1 loops=570105) Output: customer_1.c_customer_sk, customer_1.c_customer_id, customer_1.c_current_cdemo_sk, customer_1.c_current_hdemo_sk, customer_1.c_current_addr_sk, customer_1.c_first_shipto_date_sk, customer_1.c_first_sales_date_sk, customer_1.c_salutation, customer_1.c_first_name, customer_1.c_last_name, customer_1.c_preferred_cust_flag, customer_1.c_birth_day, customer_1.c_birth_month, customer_1.c_birth_year, customer_1.c_birth_country, customer_1.c_login, customer_1.c_email_address, customer_1.c_last_review_date_sk Index Cond: (customer_1.c_customer_sk = catalog_sales.cs_bill_customer_sk) Worker 0: actual time=0.001..0.001 rows=1 loops=269122 -> Subquery Scan on "*SELECT* 3" (cost=50608.89..51432.30 rows=5970 width=21) (actual time=307.757..335.810 rows=23737 loops=1) Output: "*SELECT* 3".c_last_name, "*SELECT* 3".c_first_name, "*SELECT* 3".d_date, 1 -> Unique (cost=50608.89..51372.60 rows=5970 width=17) (actual time=307.755..333.304 rows=23737 loops=1) Output: customer_2.c_last_name, customer_2.c_first_name, date_dim_2.d_date -> Gather Merge (cost=50608.89..51327.82 rows=5970 width=17) (actual time=307.754..330.282 rows=24175 loops=1) Output: customer_2.c_last_name, customer_2.c_first_name, date_dim_2.d_date Workers Planned: 2 Workers Launched: 2 -> Unique (cost=49608.86..49638.71 rows=2985 width=17) (actual time=303.752..318.599 rows=8058 loops=3) Output: customer_2.c_last_name, customer_2.c_first_name, date_dim_2.d_date Worker 0: actual time=304.217..319.193 rows=8146 loops=1 Worker 1: actual time=300.725..315.145 rows=7821 loops=1 -> Sort (cost=49608.86..49616.33 rows=2985 width=17) (actual time=303.751..307.798 rows=95920 loops=3) Output: customer_2.c_last_name, customer_2.c_first_name, date_dim_2.d_date Sort Key: customer_2.c_last_name, customer_2.c_first_name, date_dim_2.d_date Sort Method: quicksort Memory: 7446kB Worker 0: actual time=304.216..308.298 rows=96768 loops=1 Sort Method: quicksort Memory: 7377kB Worker 1: actual time=300.724..304.578 rows=92852 loops=1 Sort Method: quicksort Memory: 7205kB -> Nested Loop (cost=2570.65..49436.58 rows=2985 width=17) (actual time=3.708..232.553 rows=95920 loops=3) Output: customer_2.c_last_name, customer_2.c_first_name, date_dim_2.d_date Inner Unique: true Worker 0: actual time=2.743..231.575 rows=96768 loops=1 Worker 1: actual time=3.080..232.464 rows=92852 loops=1 -> Parallel Hash Join (cost=2570.23..48102.58 rows=2985 width=8) (actual time=3.688..99.912 rows=95936 loops=3) Output: web_sales.ws_bill_customer_sk, date_dim_2.d_date Inner Unique: true Hash Cond: (web_sales.ws_sold_date_sk = date_dim_2.d_date_sk) Worker 0: actual time=2.717..98.228 rows=96786 loops=1 Worker 1: actual time=3.058..98.845 rows=92874 loops=1 -> Parallel Seq Scan on public.web_sales (cost=0.00..43955.13 rows=600813 width=8) (actual time=0.012..53.735 rows=480649 loops=3) Output: web_sales.ws_sold_date_sk, web_sales.ws_sold_time_sk, web_sales.ws_ship_date_sk, web_sales.ws_item_sk, web_sales.ws_bill_customer_sk, web_sales.ws_bill_cdemo_sk, web_sales.ws_bill_hdemo_sk, web_sales.ws_bill_addr_sk, web_sales.ws_ship_customer_sk, web_sales.ws_ship_cdemo_sk, web_sales.ws_ship_hdemo_sk, web_sales.ws_ship_addr_sk, web_sales.ws_web_page_sk, web_sales.ws_web_site_sk, web_sales.ws_ship_mode_sk, web_sales.ws_warehouse_sk, web_sales.ws_promo_sk, web_sales.ws_order_number, web_sales.ws_quantity, web_sales.ws_wholesale_cost, web_sales.ws_list_price, web_sales.ws_sales_price, web_sales.ws_ext_discount_amt, web_sales.ws_ext_sales_price, web_sales.ws_ext_wholesale_cost, web_sales.ws_ext_list_price, web_sales.ws_ext_tax, web_sales.ws_coupon_amt, web_sales.ws_ext_ship_cost, web_sales.ws_net_paid, web_sales.ws_net_paid_inc_tax, web_sales.ws_net_paid_inc_ship, web_sales.ws_net_paid_inc_ship_tax, web_sales.ws_net_profit Worker 0: actual time=0.015..52.402 rows=481473 loops=1 Worker 1: actual time=0.014..54.245 rows=472826 loops=1 -> Parallel Hash (cost=2567.55..2567.55 rows=214 width=8) (actual time=3.508..3.509 rows=122 loops=3) Output: date_dim_2.d_date, date_dim_2.d_date_sk Buckets: 1024 Batches: 1 Memory Usage: 104kB Worker 0: actual time=2.634..2.635 rows=76 loops=1 Worker 1: actual time=2.932..2.933 rows=99 loops=1 -> Parallel Seq Scan on public.date_dim date_dim_2 (cost=0.00..2567.55 rows=214 width=8) (actual time=1.731..3.452 rows=122 loops=3) Output: date_dim_2.d_date, date_dim_2.d_date_sk Filter: ((date_dim_2.d_month_seq >= 1176) AND (date_dim_2.d_month_seq <= 1187)) Rows Removed by Filter: 24228 Worker 0: actual time=0.853..2.568 rows=76 loops=1 Worker 1: actual time=1.155..2.874 rows=99 loops=1 -> Index Scan using customer_pkey on public.customer customer_2 (cost=0.42..0.45 rows=1 width=17) (actual time=0.001..0.001 rows=1 loops=287809) Output: customer_2.c_customer_sk, customer_2.c_customer_id, customer_2.c_current_cdemo_sk, customer_2.c_current_hdemo_sk, customer_2.c_current_addr_sk, customer_2.c_first_shipto_date_sk, customer_2.c_first_sales_date_sk, customer_2.c_salutation, customer_2.c_first_name, customer_2.c_last_name, customer_2.c_preferred_cust_flag, customer_2.c_birth_day, customer_2.c_birth_month, customer_2.c_birth_year, customer_2.c_birth_country, customer_2.c_login, customer_2.c_email_address, customer_2.c_last_review_date_sk Index Cond: (customer_2.c_customer_sk = web_sales.ws_bill_customer_sk) Worker 0: actual time=0.001..0.001 rows=1 loops=96786 Worker 1: actual time=0.001..0.001 rows=1 loops=92874 Planning Time: 2.935 ms Execution Time: 2588.373 ms (147 rows) QUERY PLAN -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Aggregate (cost=252319.25..252319.26 rows=1 width=8) (actual time=2185.816..2200.051 rows=1 loops=1) Output: count(*) -> Subquery Scan on cool_cust (cost=149504.61..252278.56 rows=16276 width=0) (actual time=2169.488..2196.223 rows=93140 loops=1) Output: cool_cust.c_last_name, cool_cust.c_first_name, cool_cust.d_date -> HashSetOp Except (cost=149504.61..252115.80 rows=16276 width=144) (actual time=2169.487..2187.308 rows=93140 loops=1) Output: "*SELECT* 1".c_last_name, "*SELECT* 1".c_first_name, "*SELECT* 1".d_date, (0) -> Append (cost=149504.61..251941.76 rows=23205 width=144) (actual time=1712.746..2160.576 rows=117004 loops=1) -> Result (cost=149504.61..200338.87 rows=16276 width=144) (actual time=1712.746..1750.336 rows=93267 loops=1) Output: "*SELECT* 1".c_last_name, "*SELECT* 1".c_first_name, "*SELECT* 1".d_date, 0 -> HashSetOp Except (cost=149504.61..200176.11 rows=16276 width=144) (actual time=1712.744..1740.325 rows=93267 loops=1) Output: "*SELECT* 1".c_last_name, "*SELECT* 1".c_first_name, "*SELECT* 1".d_date, (0) -> Append (cost=149504.61..199992.99 rows=24415 width=144) (actual time=973.112..1698.406 rows=156635 loops=1) -> Subquery Scan on "*SELECT* 1" (cost=149504.61..149830.13 rows=16276 width=21) (actual time=973.111..1013.840 rows=93891 loops=1) Output: "*SELECT* 1".c_last_name, "*SELECT* 1".c_first_name, "*SELECT* 1".d_date, 0 -> Unique (cost=149504.61..149667.37 rows=16276 width=17) (actual time=973.109..1004.160 rows=93891 loops=1) Output: customer.c_last_name, customer.c_first_name, date_dim.d_date -> Sort (cost=149504.61..149545.30 rows=16276 width=17) (actual time=973.108..992.133 rows=94197 loops=1) Output: customer.c_last_name, customer.c_first_name, date_dim.d_date Sort Key: customer.c_last_name, customer.c_first_name, date_dim.d_date Sort Method: quicksort Memory: 7280kB -> Gather (cost=146575.70..148366.06 rows=16276 width=17) (actual time=820.555..850.480 rows=94197 loops=1) Output: customer.c_last_name, customer.c_first_name, date_dim.d_date Workers Planned: 1 Workers Launched: 1 -> HashAggregate (cost=145575.70..145738.46 rows=16276 width=17) (actual time=818.787..827.198 rows=47098 loops=2) Output: customer.c_last_name, customer.c_first_name, date_dim.d_date Group Key: customer.c_last_name, customer.c_first_name, date_dim.d_date Batches: 1 Memory Usage: 5649kB Worker 0: actual time=817.326..826.427 rows=46986 loops=1 Batches: 1 Memory Usage: 5649kB -> Parallel Hash Join (cost=140703.75..145453.63 rows=16276 width=17) (actual time=608.035..706.762 rows=533434 loops=2) Output: customer.c_last_name, customer.c_first_name, date_dim.d_date Hash Cond: (customer.c_customer_sk = store_sales.ss_customer_sk) Worker 0: actual time=606.747..705.672 rows=531889 loops=1 -> Parallel Seq Scan on public.customer (cost=0.00..3838.06 rows=84706 width=17) (actual time=0.009..7.236 rows=72000 loops=2) Output: customer.c_customer_sk, customer.c_customer_id, customer.c_current_cdemo_sk, customer.c_current_hdemo_sk, customer.c_current_addr_sk, customer.c_first_shipto_date_sk, customer.c_first_sales_date_sk, customer.c_salutation, customer.c_first_name, customer.c_last_name, customer.c_preferred_cust_flag, customer.c_birth_day, customer.c_birth_month, customer.c_birth_year, customer.c_birth_country, customer.c_login, customer.c_email_address, customer.c_last_review_date_sk Worker 0: actual time=0.010..7.344 rows=71598 loops=1 -> Parallel Hash (cost=140559.64..140559.64 rows=11529 width=8) (actual time=607.826..607.830 rows=546248 loops=2) Output: store_sales.ss_customer_sk, date_dim.d_date Buckets: 2097152 (originally 32768) Batches: 1 (originally 1) Memory Usage: 74272kB Worker 0: actual time=606.664..606.666 rows=546687 loops=1 -> Parallel Hash Join (cost=2570.12..140559.64 rows=11529 width=8) (actual time=5.418..506.881 rows=546248 loops=2) Output: store_sales.ss_customer_sk, date_dim.d_date Inner Unique: true Hash Cond: (store_sales.ss_sold_date_sk = date_dim.d_date_sk) Worker 0: actual time=4.257..506.966 rows=546687 loops=1 -> Parallel Seq Scan on public.store_sales (cost=0.00..131690.80 rows=2399380 width=8) (actual time=0.019..257.782 rows=2879322 loops=2) Output: store_sales.ss_sold_date_sk, store_sales.ss_sold_time_sk, store_sales.ss_item_sk, store_sales.ss_customer_sk, store_sales.ss_cdemo_sk, store_sales.ss_hdemo_sk, store_sales.ss_addr_sk, store_sales.ss_store_sk, store_sales.ss_promo_sk, store_sales.ss_ticket_number, store_sales.ss_quantity, store_sales.ss_wholesale_cost, store_sales.ss_list_price, store_sales.ss_sales_price, store_sales.ss_ext_discount_amt, store_sales.ss_ext_sales_price, store_sales.ss_ext_wholesale_cost, store_sales.ss_ext_list_price, store_sales.ss_ext_tax, store_sales.ss_coupon_amt, store_sales.ss_net_paid, store_sales.ss_net_paid_inc_tax, store_sales.ss_net_profit Worker 0: actual time=0.022..257.891 rows=2878318 loops=1 -> Parallel Hash (cost=2567.55..2567.55 rows=206 width=8) (actual time=5.371..5.371 rows=182 loops=2) Output: date_dim.d_date, date_dim.d_date_sk Buckets: 1024 Batches: 1 Memory Usage: 72kB Worker 0: actual time=4.204..4.205 rows=155 loops=1 -> Parallel Seq Scan on public.date_dim (cost=0.00..2567.55 rows=206 width=8) (actual time=2.189..5.321 rows=182 loops=2) Output: date_dim.d_date, date_dim.d_date_sk Filter: ((date_dim.d_month_seq >= 1176) AND (date_dim.d_month_seq <= 1187)) Rows Removed by Filter: 36342 Worker 0: actual time=1.028..4.157 rows=155 loops=1 -> Subquery Scan on "*SELECT* 2" (cost=49878.01..50040.79 rows=8139 width=21) (actual time=658.619..676.077 rows=62744 loops=1) Output: "*SELECT* 2".c_last_name, "*SELECT* 2".c_first_name, "*SELECT* 2".d_date, 1 -> Unique (cost=49878.01..49959.40 rows=8139 width=17) (actual time=658.617..669.592 rows=62744 loops=1) Output: customer_1.c_last_name, customer_1.c_first_name, date_dim_1.d_date -> Sort (cost=49878.01..49898.36 rows=8139 width=17) (actual time=658.616..661.616 rows=62744 loops=1) Output: customer_1.c_last_name, customer_1.c_first_name, date_dim_1.d_date Sort Key: customer_1.c_last_name, customer_1.c_first_name, date_dim_1.d_date Sort Method: quicksort Memory: 4341kB -> Gather (cost=48454.07..49349.36 rows=8139 width=17) (actual time=561.197..571.678 rows=62744 loops=1) Output: customer_1.c_last_name, customer_1.c_first_name, date_dim_1.d_date Workers Planned: 1 Workers Launched: 1 -> HashAggregate (cost=47454.07..47535.46 rows=8139 width=17) (actual time=535.356..541.636 rows=31372 loops=2) Output: customer_1.c_last_name, customer_1.c_first_name, date_dim_1.d_date Group Key: customer_1.c_last_name, customer_1.c_first_name, date_dim_1.d_date Batches: 1 Memory Usage: 3601kB Worker 0: actual time=509.782..515.593 rows=29639 loops=1 Batches: 1 Memory Usage: 3601kB -> Nested Loop (cost=0.85..47393.02 rows=8139 width=17) (actual time=1.863..466.491 rows=284349 loops=2) Output: customer_1.c_last_name, customer_1.c_first_name, date_dim_1.d_date Inner Unique: true Worker 0: actual time=0.549..444.467 rows=268480 loops=1 -> Nested Loop (cost=0.43..43793.90 rows=8139 width=8) (actual time=1.847..88.255 rows=285052 loops=2) Output: catalog_sales.cs_bill_customer_sk, date_dim_1.d_date Worker 0: actual time=0.527..85.258 rows=269122 loops=1 -> Parallel Seq Scan on public.date_dim date_dim_1 (cost=0.00..2567.55 rows=206 width=8) (actual time=1.809..3.772 rows=182 loops=2) Output: date_dim_1.d_date_sk, date_dim_1.d_date_id, date_dim_1.d_date, date_dim_1.d_month_seq, date_dim_1.d_week_seq, date_dim_1.d_quarter_seq, date_dim_1.d_year, date_dim_1.d_dow, date_dim_1.d_moy, date_dim_1.d_dom, date_dim_1.d_qoy, date_dim_1.d_fy_year, date_dim_1.d_fy_quarter_seq, date_dim_1.d_fy_week_seq, date_dim_1.d_day_name, date_dim_1.d_quarter_name, date_dim_1.d_holiday, date_dim_1.d_weekend, date_dim_1.d_following_holiday, date_dim_1.d_first_dom, date_dim_1.d_last_dom, date_dim_1.d_same_day_ly, date_dim_1.d_same_day_lq, date_dim_1.d_current_day, date_dim_1.d_current_week, date_dim_1.d_current_month, date_dim_1.d_current_quarter, date_dim_1.d_current_year Filter: ((date_dim_1.d_month_seq >= 1176) AND (date_dim_1.d_month_seq <= 1187)) Rows Removed by Filter: 36342 Worker 0: actual time=0.489..4.356 rows=175 loops=1 -> Index Scan using idx_cs_sold_date_sk on public.catalog_sales (cost=0.43..184.53 rows=1560 width=8) (actual time=0.004..0.314 rows=1562 loops=365) Output: catalog_sales.cs_sold_date_sk, catalog_sales.cs_sold_time_sk, catalog_sales.cs_ship_date_sk, catalog_sales.cs_bill_customer_sk, catalog_sales.cs_bill_cdemo_sk, catalog_sales.cs_bill_hdemo_sk, catalog_sales.cs_bill_addr_sk, catalog_sales.cs_ship_customer_sk, catalog_sales.cs_ship_cdemo_sk, catalog_sales.cs_ship_hdemo_sk, catalog_sales.cs_ship_addr_sk, catalog_sales.cs_call_center_sk, catalog_sales.cs_catalog_page_sk, catalog_sales.cs_ship_mode_sk, catalog_sales.cs_warehouse_sk, catalog_sales.cs_item_sk, catalog_sales.cs_promo_sk, catalog_sales.cs_order_number, catalog_sales.cs_quantity, catalog_sales.cs_wholesale_cost, catalog_sales.cs_list_price, catalog_sales.cs_sales_price, catalog_sales.cs_ext_discount_amt, catalog_sales.cs_ext_sales_price, catalog_sales.cs_ext_wholesale_cost, catalog_sales.cs_ext_list_price, catalog_sales.cs_ext_tax, catalog_sales.cs_coupon_amt, catalog_sales.cs_ext_ship_cost, catalog_sales.cs_net_paid, catalog_sales.cs_net_paid_inc_tax, catalog_sales.cs_net_paid_inc_ship, catalog_sales.cs_net_paid_inc_ship_tax, catalog_sales.cs_net_profit Index Cond: (catalog_sales.cs_sold_date_sk = date_dim_1.d_date_sk) Worker 0: actual time=0.004..0.315 rows=1538 loops=175 -> Index Scan using customer_pkey on public.customer customer_1 (cost=0.42..0.44 rows=1 width=17) (actual time=0.001..0.001 rows=1 loops=570105) Output: customer_1.c_customer_sk, customer_1.c_customer_id, customer_1.c_current_cdemo_sk, customer_1.c_current_hdemo_sk, customer_1.c_current_addr_sk, customer_1.c_first_shipto_date_sk, customer_1.c_first_sales_date_sk, customer_1.c_salutation, customer_1.c_first_name, customer_1.c_last_name, customer_1.c_preferred_cust_flag, customer_1.c_birth_day, customer_1.c_birth_month, customer_1.c_birth_year, customer_1.c_birth_country, customer_1.c_login, customer_1.c_email_address, customer_1.c_last_review_date_sk Index Cond: (customer_1.c_customer_sk = catalog_sales.cs_bill_customer_sk) Worker 0: actual time=0.001..0.001 rows=1 loops=269122 -> Subquery Scan on "*SELECT* 3" (cost=50558.61..51486.87 rows=6929 width=21) (actual time=308.103..403.898 rows=23737 loops=1) Output: "*SELECT* 3".c_last_name, "*SELECT* 3".c_first_name, "*SELECT* 3".d_date, 1 -> Unique (cost=50558.61..51417.58 rows=6929 width=17) (actual time=308.101..401.424 rows=23737 loops=1) Output: customer_2.c_last_name, customer_2.c_first_name, date_dim_2.d_date -> Gather Merge (cost=50558.61..51365.61 rows=6929 width=17) (actual time=308.100..370.805 rows=287761 loops=1) Output: customer_2.c_last_name, customer_2.c_first_name, date_dim_2.d_date Workers Planned: 2 Workers Launched: 2 -> Sort (cost=49558.59..49565.81 rows=2887 width=17) (actual time=304.699..310.446 rows=95920 loops=3) Output: customer_2.c_last_name, customer_2.c_first_name, date_dim_2.d_date Sort Key: customer_2.c_last_name, customer_2.c_first_name, date_dim_2.d_date Sort Method: quicksort Memory: 7444kB Worker 0: actual time=304.767..309.513 rows=96821 loops=1 Sort Method: quicksort Memory: 7379kB Worker 1: actual time=301.512..309.827 rows=92877 loops=1 Sort Method: quicksort Memory: 7205kB -> Nested Loop (cost=2570.55..49392.65 rows=2887 width=17) (actual time=3.419..230.307 rows=95920 loops=3) Output: customer_2.c_last_name, customer_2.c_first_name, date_dim_2.d_date Inner Unique: true Worker 0: actual time=2.440..229.401 rows=96821 loops=1 Worker 1: actual time=2.704..230.216 rows=92877 loops=1 -> Parallel Hash Join (cost=2570.12..48102.45 rows=2887 width=8) (actual time=3.401..99.460 rows=95936 loops=3) Output: web_sales.ws_bill_customer_sk, date_dim_2.d_date Inner Unique: true Hash Cond: (web_sales.ws_sold_date_sk = date_dim_2.d_date_sk) Worker 0: actual time=2.417..97.885 rows=96841 loops=1 Worker 1: actual time=2.685..97.490 rows=92890 loops=1 -> Parallel Seq Scan on public.web_sales (cost=0.00..43955.11 rows=600811 width=8) (actual time=0.016..53.317 rows=480649 loops=3) Output: web_sales.ws_sold_date_sk, web_sales.ws_sold_time_sk, web_sales.ws_ship_date_sk, web_sales.ws_item_sk, web_sales.ws_bill_customer_sk, web_sales.ws_bill_cdemo_sk, web_sales.ws_bill_hdemo_sk, web_sales.ws_bill_addr_sk, web_sales.ws_ship_customer_sk, web_sales.ws_ship_cdemo_sk, web_sales.ws_ship_hdemo_sk, web_sales.ws_ship_addr_sk, web_sales.ws_web_page_sk, web_sales.ws_web_site_sk, web_sales.ws_ship_mode_sk, web_sales.ws_warehouse_sk, web_sales.ws_promo_sk, web_sales.ws_order_number, web_sales.ws_quantity, web_sales.ws_wholesale_cost, web_sales.ws_list_price, web_sales.ws_sales_price, web_sales.ws_ext_discount_amt, web_sales.ws_ext_sales_price, web_sales.ws_ext_wholesale_cost, web_sales.ws_ext_list_price, web_sales.ws_ext_tax, web_sales.ws_coupon_amt, web_sales.ws_ext_ship_cost, web_sales.ws_net_paid, web_sales.ws_net_paid_inc_tax, web_sales.ws_net_paid_inc_ship, web_sales.ws_net_paid_inc_ship_tax, web_sales.ws_net_profit Worker 0: actual time=0.019..52.418 rows=484786 loops=1 Worker 1: actual time=0.020..53.691 rows=460693 loops=1 -> Parallel Hash (cost=2567.55..2567.55 rows=206 width=8) (actual time=3.208..3.209 rows=122 loops=3) Output: date_dim_2.d_date, date_dim_2.d_date_sk Buckets: 1024 Batches: 1 Memory Usage: 104kB Worker 0: actual time=2.256..2.257 rows=152 loops=1 Worker 1: actual time=2.557..2.558 rows=76 loops=1 -> Parallel Seq Scan on public.date_dim date_dim_2 (cost=0.00..2567.55 rows=206 width=8) (actual time=1.291..3.153 rows=122 loops=3) Output: date_dim_2.d_date, date_dim_2.d_date_sk Filter: ((date_dim_2.d_month_seq >= 1176) AND (date_dim_2.d_month_seq <= 1187)) Rows Removed by Filter: 24228 Worker 0: actual time=0.329..2.197 rows=152 loops=1 Worker 1: actual time=0.643..2.497 rows=76 loops=1 -> Index Scan using customer_pkey on public.customer customer_2 (cost=0.42..0.45 rows=1 width=17) (actual time=0.001..0.001 rows=1 loops=287809) Output: customer_2.c_customer_sk, customer_2.c_customer_id, customer_2.c_current_cdemo_sk, customer_2.c_current_hdemo_sk, customer_2.c_current_addr_sk, customer_2.c_first_shipto_date_sk, customer_2.c_first_sales_date_sk, customer_2.c_salutation, customer_2.c_first_name, customer_2.c_last_name, customer_2.c_preferred_cust_flag, customer_2.c_birth_day, customer_2.c_birth_month, customer_2.c_birth_year, customer_2.c_birth_country, customer_2.c_login, customer_2.c_email_address, customer_2.c_last_review_date_sk Index Cond: (customer_2.c_customer_sk = web_sales.ws_bill_customer_sk) Worker 0: actual time=0.001..0.001 rows=1 loops=96841 Worker 1: actual time=0.001..0.001 rows=1 loops=92890 Planning Time: 3.013 ms Execution Time: 2203.990 ms (145 rows) Attachments: [text/plain] tpcds-q87-3c6fc5820-wm50-plan.txt (26.0K, ../../[email protected]/2-tpcds-q87-3c6fc5820-wm50-plan.txt) download | inline: QUERY PLAN -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Aggregate (cost=254862.89..254862.90 rows=1 width=8) (actual time=2567.064..2581.619 rows=1 loops=1) Output: count(*) -> Subquery Scan on cool_cust (cost=147645.17..254820.81 rows=16834 width=0) (actual time=2551.091..2578.005 rows=93140 loops=1) Output: cool_cust.c_last_name, cool_cust.c_first_name, cool_cust.d_date -> HashSetOp Except (cost=147645.17..254652.47 rows=16834 width=144) (actual time=2551.090..2569.217 rows=93140 loops=1) Output: "*SELECT* 1".c_last_name, "*SELECT* 1".c_first_name, "*SELECT* 1".d_date, (0) -> Append (cost=147645.17..254481.44 rows=22804 width=144) (actual time=2162.417..2542.403 rows=117004 loops=1) -> Result (cost=147645.17..202935.12 rows=16834 width=144) (actual time=2162.416..2200.221 rows=93267 loops=1) Output: "*SELECT* 1".c_last_name, "*SELECT* 1".c_first_name, "*SELECT* 1".d_date, 0 -> HashSetOp Except (cost=147645.17..202766.78 rows=16834 width=144) (actual time=2162.414..2190.284 rows=93267 loops=1) Output: "*SELECT* 1".c_last_name, "*SELECT* 1".c_first_name, "*SELECT* 1".d_date, (0) -> Append (cost=147645.17..202577.39 rows=25251 width=144) (actual time=1175.080..2146.489 rows=156635 loops=1) -> Subquery Scan on "*SELECT* 1" (cost=147645.17..150001.93 rows=16834 width=21) (actual time=1175.079..1315.210 rows=93891 loops=1) Output: "*SELECT* 1".c_last_name, "*SELECT* 1".c_first_name, "*SELECT* 1".d_date, 0 -> Unique (cost=147645.17..149833.59 rows=16834 width=17) (actual time=1175.075..1305.410 rows=93891 loops=1) Output: customer.c_last_name, customer.c_first_name, date_dim.d_date -> Gather Merge (cost=147645.17..149707.33 rows=16834 width=17) (actual time=1175.075..1293.810 rows=94207 loops=1) Output: customer.c_last_name, customer.c_first_name, date_dim.d_date Workers Planned: 1 Workers Launched: 1 -> Unique (cost=146645.16..146813.50 rows=16834 width=17) (actual time=1171.845..1259.795 rows=47104 loops=2) Output: customer.c_last_name, customer.c_first_name, date_dim.d_date Worker 0: actual time=1168.901..1257.819 rows=47011 loops=1 -> Sort (cost=146645.16..146687.24 rows=16834 width=17) (actual time=1171.842..1199.145 rows=533434 loops=2) Output: customer.c_last_name, customer.c_first_name, date_dim.d_date Sort Key: customer.c_last_name, customer.c_first_name, date_dim.d_date Sort Method: quicksort Memory: 41416kB Worker 0: actual time=1168.898..1196.043 rows=533101 loops=1 Sort Method: quicksort Memory: 41393kB -> Parallel Hash Join (cost=140711.42..145463.49 rows=16834 width=17) (actual time=612.177..710.988 rows=533434 loops=2) Output: customer.c_last_name, customer.c_first_name, date_dim.d_date Hash Cond: (customer.c_customer_sk = store_sales.ss_customer_sk) Worker 0: actual time=611.023..709.758 rows=533101 loops=1 -> Parallel Seq Scan on public.customer (cost=0.00..3838.06 rows=84706 width=17) (actual time=0.007..6.935 rows=72000 loops=2) Output: customer.c_customer_sk, customer.c_customer_id, customer.c_current_cdemo_sk, customer.c_current_hdemo_sk, customer.c_current_addr_sk, customer.c_first_shipto_date_sk, customer.c_first_sales_date_sk, customer.c_salutation, customer.c_first_name, customer.c_last_name, customer.c_preferred_cust_flag, customer.c_birth_day, customer.c_birth_month, customer.c_birth_year, customer.c_birth_country, customer.c_login, customer.c_email_address, customer.c_last_review_date_sk Worker 0: actual time=0.012..6.906 rows=72071 loops=1 -> Parallel Hash (cost=140562.37..140562.37 rows=11924 width=8) (actual time=611.999..612.003 rows=546248 loops=2) Output: store_sales.ss_customer_sk, date_dim.d_date Buckets: 2097152 (originally 32768) Batches: 1 (originally 1) Memory Usage: 74272kB Worker 0: actual time=610.930..610.934 rows=548215 loops=1 -> Parallel Hash Join (cost=2570.23..140562.37 rows=11924 width=8) (actual time=5.025..509.830 rows=546248 loops=2) Output: store_sales.ss_customer_sk, date_dim.d_date Inner Unique: true Hash Cond: (store_sales.ss_sold_date_sk = date_dim.d_date_sk) Worker 0: actual time=3.992..508.085 rows=548215 loops=1 -> Parallel Seq Scan on public.store_sales (cost=0.00..131692.88 rows=2399588 width=8) (actual time=0.016..263.040 rows=2879322 loops=2) Output: store_sales.ss_sold_date_sk, store_sales.ss_sold_time_sk, store_sales.ss_item_sk, store_sales.ss_customer_sk, store_sales.ss_cdemo_sk, store_sales.ss_hdemo_sk, store_sales.ss_addr_sk, store_sales.ss_store_sk, store_sales.ss_promo_sk, store_sales.ss_ticket_number, store_sales.ss_quantity, store_sales.ss_wholesale_cost, store_sales.ss_list_price, store_sales.ss_sales_price, store_sales.ss_ext_discount_amt, store_sales.ss_ext_sales_price, store_sales.ss_ext_wholesale_cost, store_sales.ss_ext_list_price, store_sales.ss_ext_tax, store_sales.ss_coupon_amt, store_sales.ss_net_paid, store_sales.ss_net_paid_inc_tax, store_sales.ss_net_profit Worker 0: actual time=0.018..262.235 rows=2882396 loops=1 -> Parallel Hash (cost=2567.55..2567.55 rows=214 width=8) (actual time=4.951..4.953 rows=182 loops=2) Output: date_dim.d_date, date_dim.d_date_sk Buckets: 1024 Batches: 1 Memory Usage: 72kB Worker 0: actual time=3.877..3.879 rows=137 loops=1 -> Parallel Seq Scan on public.date_dim (cost=0.00..2567.55 rows=214 width=8) (actual time=2.164..4.905 rows=182 loops=2) Output: date_dim.d_date, date_dim.d_date_sk Filter: ((date_dim.d_month_seq >= 1176) AND (date_dim.d_month_seq <= 1187)) Rows Removed by Filter: 36342 Worker 0: actual time=1.096..3.834 rows=137 loops=1 -> Subquery Scan on "*SELECT* 2" (cost=51270.83..52449.21 rows=8417 width=21) (actual time=750.303..822.786 rows=62744 loops=1) Output: "*SELECT* 2".c_last_name, "*SELECT* 2".c_first_name, "*SELECT* 2".d_date, 1 -> Unique (cost=51270.83..52365.04 rows=8417 width=17) (actual time=750.300..816.251 rows=62744 loops=1) Output: customer_1.c_last_name, customer_1.c_first_name, date_dim_1.d_date -> Gather Merge (cost=51270.83..52301.92 rows=8417 width=17) (actual time=750.299..808.594 rows=62744 loops=1) Output: customer_1.c_last_name, customer_1.c_first_name, date_dim_1.d_date Workers Planned: 1 Workers Launched: 1 -> Unique (cost=50270.82..50354.99 rows=8417 width=17) (actual time=720.125..765.136 rows=31372 loops=2) Output: customer_1.c_last_name, customer_1.c_first_name, date_dim_1.d_date Worker 0: actual time=690.255..733.699 rows=29639 loops=1 -> Sort (cost=50270.82..50291.87 rows=8417 width=17) (actual time=720.123..732.766 rows=284349 loops=2) Output: customer_1.c_last_name, customer_1.c_first_name, date_dim_1.d_date Sort Key: customer_1.c_last_name, customer_1.c_first_name, date_dim_1.d_date Sort Method: quicksort Memory: 25655kB Worker 0: actual time=690.253..702.224 rows=268480 loops=1 Sort Method: quicksort Memory: 24250kB -> Nested Loop (cost=0.85..49722.07 rows=8417 width=17) (actual time=1.831..466.098 rows=284349 loops=2) Output: customer_1.c_last_name, customer_1.c_first_name, date_dim_1.d_date Inner Unique: true Worker 0: actual time=0.526..449.880 rows=268480 loops=1 -> Nested Loop (cost=0.43..46000.01 rows=8417 width=8) (actual time=1.811..91.963 rows=285052 loops=2) Output: catalog_sales.cs_bill_customer_sk, date_dim_1.d_date Worker 0: actual time=0.500..90.643 rows=269122 loops=1 -> Parallel Seq Scan on public.date_dim date_dim_1 (cost=0.00..2567.55 rows=214 width=8) (actual time=1.774..4.005 rows=182 loops=2) Output: date_dim_1.d_date_sk, date_dim_1.d_date_id, date_dim_1.d_date, date_dim_1.d_month_seq, date_dim_1.d_week_seq, date_dim_1.d_quarter_seq, date_dim_1.d_year, date_dim_1.d_dow, date_dim_1.d_moy, date_dim_1.d_dom, date_dim_1.d_qoy, date_dim_1.d_fy_year, date_dim_1.d_fy_quarter_seq, date_dim_1.d_fy_week_seq, date_dim_1.d_day_name, date_dim_1.d_quarter_name, date_dim_1.d_holiday, date_dim_1.d_weekend, date_dim_1.d_following_holiday, date_dim_1.d_first_dom, date_dim_1.d_last_dom, date_dim_1.d_same_day_ly, date_dim_1.d_same_day_lq, date_dim_1.d_current_day, date_dim_1.d_current_week, date_dim_1.d_current_month, date_dim_1.d_current_quarter, date_dim_1.d_current_year Filter: ((date_dim_1.d_month_seq >= 1176) AND (date_dim_1.d_month_seq <= 1187)) Rows Removed by Filter: 36342 Worker 0: actual time=0.461..4.861 rows=175 loops=1 -> Index Scan using idx_cs_sold_date_sk on public.catalog_sales (cost=0.43..187.36 rows=1560 width=8) (actual time=0.004..0.328 rows=1562 loops=365) Output: catalog_sales.cs_sold_date_sk, catalog_sales.cs_sold_time_sk, catalog_sales.cs_ship_date_sk, catalog_sales.cs_bill_customer_sk, catalog_sales.cs_bill_cdemo_sk, catalog_sales.cs_bill_hdemo_sk, catalog_sales.cs_bill_addr_sk, catalog_sales.cs_ship_customer_sk, catalog_sales.cs_ship_cdemo_sk, catalog_sales.cs_ship_hdemo_sk, catalog_sales.cs_ship_addr_sk, catalog_sales.cs_call_center_sk, catalog_sales.cs_catalog_page_sk, catalog_sales.cs_ship_mode_sk, catalog_sales.cs_warehouse_sk, catalog_sales.cs_item_sk, catalog_sales.cs_promo_sk, catalog_sales.cs_order_number, catalog_sales.cs_quantity, catalog_sales.cs_wholesale_cost, catalog_sales.cs_list_price, catalog_sales.cs_sales_price, catalog_sales.cs_ext_discount_amt, catalog_sales.cs_ext_sales_price, catalog_sales.cs_ext_wholesale_cost, catalog_sales.cs_ext_list_price, catalog_sales.cs_ext_tax, catalog_sales.cs_coupon_amt, catalog_sales.cs_ext_ship_cost, catalog_sales.cs_net_paid, catalog_sales.cs_net_paid_inc_tax, catalog_sales.cs_net_paid_inc_ship, catalog_sales.cs_net_paid_inc_ship_tax, catalog_sales.cs_net_profit Index Cond: (catalog_sales.cs_sold_date_sk = date_dim_1.d_date_sk) Worker 0: actual time=0.004..0.333 rows=1538 loops=175 -> Index Scan using customer_pkey on public.customer customer_1 (cost=0.42..0.44 rows=1 width=17) (actual time=0.001..0.001 rows=1 loops=570105) Output: customer_1.c_customer_sk, customer_1.c_customer_id, customer_1.c_current_cdemo_sk, customer_1.c_current_hdemo_sk, customer_1.c_current_addr_sk, customer_1.c_first_shipto_date_sk, customer_1.c_first_sales_date_sk, customer_1.c_salutation, customer_1.c_first_name, customer_1.c_last_name, customer_1.c_preferred_cust_flag, customer_1.c_birth_day, customer_1.c_birth_month, customer_1.c_birth_year, customer_1.c_birth_country, customer_1.c_login, customer_1.c_email_address, customer_1.c_last_review_date_sk Index Cond: (customer_1.c_customer_sk = catalog_sales.cs_bill_customer_sk) Worker 0: actual time=0.001..0.001 rows=1 loops=269122 -> Subquery Scan on "*SELECT* 3" (cost=50608.89..51432.30 rows=5970 width=21) (actual time=307.757..335.810 rows=23737 loops=1) Output: "*SELECT* 3".c_last_name, "*SELECT* 3".c_first_name, "*SELECT* 3".d_date, 1 -> Unique (cost=50608.89..51372.60 rows=5970 width=17) (actual time=307.755..333.304 rows=23737 loops=1) Output: customer_2.c_last_name, customer_2.c_first_name, date_dim_2.d_date -> Gather Merge (cost=50608.89..51327.82 rows=5970 width=17) (actual time=307.754..330.282 rows=24175 loops=1) Output: customer_2.c_last_name, customer_2.c_first_name, date_dim_2.d_date Workers Planned: 2 Workers Launched: 2 -> Unique (cost=49608.86..49638.71 rows=2985 width=17) (actual time=303.752..318.599 rows=8058 loops=3) Output: customer_2.c_last_name, customer_2.c_first_name, date_dim_2.d_date Worker 0: actual time=304.217..319.193 rows=8146 loops=1 Worker 1: actual time=300.725..315.145 rows=7821 loops=1 -> Sort (cost=49608.86..49616.33 rows=2985 width=17) (actual time=303.751..307.798 rows=95920 loops=3) Output: customer_2.c_last_name, customer_2.c_first_name, date_dim_2.d_date Sort Key: customer_2.c_last_name, customer_2.c_first_name, date_dim_2.d_date Sort Method: quicksort Memory: 7446kB Worker 0: actual time=304.216..308.298 rows=96768 loops=1 Sort Method: quicksort Memory: 7377kB Worker 1: actual time=300.724..304.578 rows=92852 loops=1 Sort Method: quicksort Memory: 7205kB -> Nested Loop (cost=2570.65..49436.58 rows=2985 width=17) (actual time=3.708..232.553 rows=95920 loops=3) Output: customer_2.c_last_name, customer_2.c_first_name, date_dim_2.d_date Inner Unique: true Worker 0: actual time=2.743..231.575 rows=96768 loops=1 Worker 1: actual time=3.080..232.464 rows=92852 loops=1 -> Parallel Hash Join (cost=2570.23..48102.58 rows=2985 width=8) (actual time=3.688..99.912 rows=95936 loops=3) Output: web_sales.ws_bill_customer_sk, date_dim_2.d_date Inner Unique: true Hash Cond: (web_sales.ws_sold_date_sk = date_dim_2.d_date_sk) Worker 0: actual time=2.717..98.228 rows=96786 loops=1 Worker 1: actual time=3.058..98.845 rows=92874 loops=1 -> Parallel Seq Scan on public.web_sales (cost=0.00..43955.13 rows=600813 width=8) (actual time=0.012..53.735 rows=480649 loops=3) Output: web_sales.ws_sold_date_sk, web_sales.ws_sold_time_sk, web_sales.ws_ship_date_sk, web_sales.ws_item_sk, web_sales.ws_bill_customer_sk, web_sales.ws_bill_cdemo_sk, web_sales.ws_bill_hdemo_sk, web_sales.ws_bill_addr_sk, web_sales.ws_ship_customer_sk, web_sales.ws_ship_cdemo_sk, web_sales.ws_ship_hdemo_sk, web_sales.ws_ship_addr_sk, web_sales.ws_web_page_sk, web_sales.ws_web_site_sk, web_sales.ws_ship_mode_sk, web_sales.ws_warehouse_sk, web_sales.ws_promo_sk, web_sales.ws_order_number, web_sales.ws_quantity, web_sales.ws_wholesale_cost, web_sales.ws_list_price, web_sales.ws_sales_price, web_sales.ws_ext_discount_amt, web_sales.ws_ext_sales_price, web_sales.ws_ext_wholesale_cost, web_sales.ws_ext_list_price, web_sales.ws_ext_tax, web_sales.ws_coupon_amt, web_sales.ws_ext_ship_cost, web_sales.ws_net_paid, web_sales.ws_net_paid_inc_tax, web_sales.ws_net_paid_inc_ship, web_sales.ws_net_paid_inc_ship_tax, web_sales.ws_net_profit Worker 0: actual time=0.015..52.402 rows=481473 loops=1 Worker 1: actual time=0.014..54.245 rows=472826 loops=1 -> Parallel Hash (cost=2567.55..2567.55 rows=214 width=8) (actual time=3.508..3.509 rows=122 loops=3) Output: date_dim_2.d_date, date_dim_2.d_date_sk Buckets: 1024 Batches: 1 Memory Usage: 104kB Worker 0: actual time=2.634..2.635 rows=76 loops=1 Worker 1: actual time=2.932..2.933 rows=99 loops=1 -> Parallel Seq Scan on public.date_dim date_dim_2 (cost=0.00..2567.55 rows=214 width=8) (actual time=1.731..3.452 rows=122 loops=3) Output: date_dim_2.d_date, date_dim_2.d_date_sk Filter: ((date_dim_2.d_month_seq >= 1176) AND (date_dim_2.d_month_seq <= 1187)) Rows Removed by Filter: 24228 Worker 0: actual time=0.853..2.568 rows=76 loops=1 Worker 1: actual time=1.155..2.874 rows=99 loops=1 -> Index Scan using customer_pkey on public.customer customer_2 (cost=0.42..0.45 rows=1 width=17) (actual time=0.001..0.001 rows=1 loops=287809) Output: customer_2.c_customer_sk, customer_2.c_customer_id, customer_2.c_current_cdemo_sk, customer_2.c_current_hdemo_sk, customer_2.c_current_addr_sk, customer_2.c_first_shipto_date_sk, customer_2.c_first_sales_date_sk, customer_2.c_salutation, customer_2.c_first_name, customer_2.c_last_name, customer_2.c_preferred_cust_flag, customer_2.c_birth_day, customer_2.c_birth_month, customer_2.c_birth_year, customer_2.c_birth_country, customer_2.c_login, customer_2.c_email_address, customer_2.c_last_review_date_sk Index Cond: (customer_2.c_customer_sk = web_sales.ws_bill_customer_sk) Worker 0: actual time=0.001..0.001 rows=1 loops=96786 Worker 1: actual time=0.001..0.001 rows=1 loops=92874 Planning Time: 2.935 ms Execution Time: 2588.373 ms (147 rows) [text/plain] tpcds-q87-e5b8a4c09-wm50-plan.txt (25.5K, ../../[email protected]/3-tpcds-q87-e5b8a4c09-wm50-plan.txt) download | inline: QUERY PLAN -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Aggregate (cost=252319.25..252319.26 rows=1 width=8) (actual time=2185.816..2200.051 rows=1 loops=1) Output: count(*) -> Subquery Scan on cool_cust (cost=149504.61..252278.56 rows=16276 width=0) (actual time=2169.488..2196.223 rows=93140 loops=1) Output: cool_cust.c_last_name, cool_cust.c_first_name, cool_cust.d_date -> HashSetOp Except (cost=149504.61..252115.80 rows=16276 width=144) (actual time=2169.487..2187.308 rows=93140 loops=1) Output: "*SELECT* 1".c_last_name, "*SELECT* 1".c_first_name, "*SELECT* 1".d_date, (0) -> Append (cost=149504.61..251941.76 rows=23205 width=144) (actual time=1712.746..2160.576 rows=117004 loops=1) -> Result (cost=149504.61..200338.87 rows=16276 width=144) (actual time=1712.746..1750.336 rows=93267 loops=1) Output: "*SELECT* 1".c_last_name, "*SELECT* 1".c_first_name, "*SELECT* 1".d_date, 0 -> HashSetOp Except (cost=149504.61..200176.11 rows=16276 width=144) (actual time=1712.744..1740.325 rows=93267 loops=1) Output: "*SELECT* 1".c_last_name, "*SELECT* 1".c_first_name, "*SELECT* 1".d_date, (0) -> Append (cost=149504.61..199992.99 rows=24415 width=144) (actual time=973.112..1698.406 rows=156635 loops=1) -> Subquery Scan on "*SELECT* 1" (cost=149504.61..149830.13 rows=16276 width=21) (actual time=973.111..1013.840 rows=93891 loops=1) Output: "*SELECT* 1".c_last_name, "*SELECT* 1".c_first_name, "*SELECT* 1".d_date, 0 -> Unique (cost=149504.61..149667.37 rows=16276 width=17) (actual time=973.109..1004.160 rows=93891 loops=1) Output: customer.c_last_name, customer.c_first_name, date_dim.d_date -> Sort (cost=149504.61..149545.30 rows=16276 width=17) (actual time=973.108..992.133 rows=94197 loops=1) Output: customer.c_last_name, customer.c_first_name, date_dim.d_date Sort Key: customer.c_last_name, customer.c_first_name, date_dim.d_date Sort Method: quicksort Memory: 7280kB -> Gather (cost=146575.70..148366.06 rows=16276 width=17) (actual time=820.555..850.480 rows=94197 loops=1) Output: customer.c_last_name, customer.c_first_name, date_dim.d_date Workers Planned: 1 Workers Launched: 1 -> HashAggregate (cost=145575.70..145738.46 rows=16276 width=17) (actual time=818.787..827.198 rows=47098 loops=2) Output: customer.c_last_name, customer.c_first_name, date_dim.d_date Group Key: customer.c_last_name, customer.c_first_name, date_dim.d_date Batches: 1 Memory Usage: 5649kB Worker 0: actual time=817.326..826.427 rows=46986 loops=1 Batches: 1 Memory Usage: 5649kB -> Parallel Hash Join (cost=140703.75..145453.63 rows=16276 width=17) (actual time=608.035..706.762 rows=533434 loops=2) Output: customer.c_last_name, customer.c_first_name, date_dim.d_date Hash Cond: (customer.c_customer_sk = store_sales.ss_customer_sk) Worker 0: actual time=606.747..705.672 rows=531889 loops=1 -> Parallel Seq Scan on public.customer (cost=0.00..3838.06 rows=84706 width=17) (actual time=0.009..7.236 rows=72000 loops=2) Output: customer.c_customer_sk, customer.c_customer_id, customer.c_current_cdemo_sk, customer.c_current_hdemo_sk, customer.c_current_addr_sk, customer.c_first_shipto_date_sk, customer.c_first_sales_date_sk, customer.c_salutation, customer.c_first_name, customer.c_last_name, customer.c_preferred_cust_flag, customer.c_birth_day, customer.c_birth_month, customer.c_birth_year, customer.c_birth_country, customer.c_login, customer.c_email_address, customer.c_last_review_date_sk Worker 0: actual time=0.010..7.344 rows=71598 loops=1 -> Parallel Hash (cost=140559.64..140559.64 rows=11529 width=8) (actual time=607.826..607.830 rows=546248 loops=2) Output: store_sales.ss_customer_sk, date_dim.d_date Buckets: 2097152 (originally 32768) Batches: 1 (originally 1) Memory Usage: 74272kB Worker 0: actual time=606.664..606.666 rows=546687 loops=1 -> Parallel Hash Join (cost=2570.12..140559.64 rows=11529 width=8) (actual time=5.418..506.881 rows=546248 loops=2) Output: store_sales.ss_customer_sk, date_dim.d_date Inner Unique: true Hash Cond: (store_sales.ss_sold_date_sk = date_dim.d_date_sk) Worker 0: actual time=4.257..506.966 rows=546687 loops=1 -> Parallel Seq Scan on public.store_sales (cost=0.00..131690.80 rows=2399380 width=8) (actual time=0.019..257.782 rows=2879322 loops=2) Output: store_sales.ss_sold_date_sk, store_sales.ss_sold_time_sk, store_sales.ss_item_sk, store_sales.ss_customer_sk, store_sales.ss_cdemo_sk, store_sales.ss_hdemo_sk, store_sales.ss_addr_sk, store_sales.ss_store_sk, store_sales.ss_promo_sk, store_sales.ss_ticket_number, store_sales.ss_quantity, store_sales.ss_wholesale_cost, store_sales.ss_list_price, store_sales.ss_sales_price, store_sales.ss_ext_discount_amt, store_sales.ss_ext_sales_price, store_sales.ss_ext_wholesale_cost, store_sales.ss_ext_list_price, store_sales.ss_ext_tax, store_sales.ss_coupon_amt, store_sales.ss_net_paid, store_sales.ss_net_paid_inc_tax, store_sales.ss_net_profit Worker 0: actual time=0.022..257.891 rows=2878318 loops=1 -> Parallel Hash (cost=2567.55..2567.55 rows=206 width=8) (actual time=5.371..5.371 rows=182 loops=2) Output: date_dim.d_date, date_dim.d_date_sk Buckets: 1024 Batches: 1 Memory Usage: 72kB Worker 0: actual time=4.204..4.205 rows=155 loops=1 -> Parallel Seq Scan on public.date_dim (cost=0.00..2567.55 rows=206 width=8) (actual time=2.189..5.321 rows=182 loops=2) Output: date_dim.d_date, date_dim.d_date_sk Filter: ((date_dim.d_month_seq >= 1176) AND (date_dim.d_month_seq <= 1187)) Rows Removed by Filter: 36342 Worker 0: actual time=1.028..4.157 rows=155 loops=1 -> Subquery Scan on "*SELECT* 2" (cost=49878.01..50040.79 rows=8139 width=21) (actual time=658.619..676.077 rows=62744 loops=1) Output: "*SELECT* 2".c_last_name, "*SELECT* 2".c_first_name, "*SELECT* 2".d_date, 1 -> Unique (cost=49878.01..49959.40 rows=8139 width=17) (actual time=658.617..669.592 rows=62744 loops=1) Output: customer_1.c_last_name, customer_1.c_first_name, date_dim_1.d_date -> Sort (cost=49878.01..49898.36 rows=8139 width=17) (actual time=658.616..661.616 rows=62744 loops=1) Output: customer_1.c_last_name, customer_1.c_first_name, date_dim_1.d_date Sort Key: customer_1.c_last_name, customer_1.c_first_name, date_dim_1.d_date Sort Method: quicksort Memory: 4341kB -> Gather (cost=48454.07..49349.36 rows=8139 width=17) (actual time=561.197..571.678 rows=62744 loops=1) Output: customer_1.c_last_name, customer_1.c_first_name, date_dim_1.d_date Workers Planned: 1 Workers Launched: 1 -> HashAggregate (cost=47454.07..47535.46 rows=8139 width=17) (actual time=535.356..541.636 rows=31372 loops=2) Output: customer_1.c_last_name, customer_1.c_first_name, date_dim_1.d_date Group Key: customer_1.c_last_name, customer_1.c_first_name, date_dim_1.d_date Batches: 1 Memory Usage: 3601kB Worker 0: actual time=509.782..515.593 rows=29639 loops=1 Batches: 1 Memory Usage: 3601kB -> Nested Loop (cost=0.85..47393.02 rows=8139 width=17) (actual time=1.863..466.491 rows=284349 loops=2) Output: customer_1.c_last_name, customer_1.c_first_name, date_dim_1.d_date Inner Unique: true Worker 0: actual time=0.549..444.467 rows=268480 loops=1 -> Nested Loop (cost=0.43..43793.90 rows=8139 width=8) (actual time=1.847..88.255 rows=285052 loops=2) Output: catalog_sales.cs_bill_customer_sk, date_dim_1.d_date Worker 0: actual time=0.527..85.258 rows=269122 loops=1 -> Parallel Seq Scan on public.date_dim date_dim_1 (cost=0.00..2567.55 rows=206 width=8) (actual time=1.809..3.772 rows=182 loops=2) Output: date_dim_1.d_date_sk, date_dim_1.d_date_id, date_dim_1.d_date, date_dim_1.d_month_seq, date_dim_1.d_week_seq, date_dim_1.d_quarter_seq, date_dim_1.d_year, date_dim_1.d_dow, date_dim_1.d_moy, date_dim_1.d_dom, date_dim_1.d_qoy, date_dim_1.d_fy_year, date_dim_1.d_fy_quarter_seq, date_dim_1.d_fy_week_seq, date_dim_1.d_day_name, date_dim_1.d_quarter_name, date_dim_1.d_holiday, date_dim_1.d_weekend, date_dim_1.d_following_holiday, date_dim_1.d_first_dom, date_dim_1.d_last_dom, date_dim_1.d_same_day_ly, date_dim_1.d_same_day_lq, date_dim_1.d_current_day, date_dim_1.d_current_week, date_dim_1.d_current_month, date_dim_1.d_current_quarter, date_dim_1.d_current_year Filter: ((date_dim_1.d_month_seq >= 1176) AND (date_dim_1.d_month_seq <= 1187)) Rows Removed by Filter: 36342 Worker 0: actual time=0.489..4.356 rows=175 loops=1 -> Index Scan using idx_cs_sold_date_sk on public.catalog_sales (cost=0.43..184.53 rows=1560 width=8) (actual time=0.004..0.314 rows=1562 loops=365) Output: catalog_sales.cs_sold_date_sk, catalog_sales.cs_sold_time_sk, catalog_sales.cs_ship_date_sk, catalog_sales.cs_bill_customer_sk, catalog_sales.cs_bill_cdemo_sk, catalog_sales.cs_bill_hdemo_sk, catalog_sales.cs_bill_addr_sk, catalog_sales.cs_ship_customer_sk, catalog_sales.cs_ship_cdemo_sk, catalog_sales.cs_ship_hdemo_sk, catalog_sales.cs_ship_addr_sk, catalog_sales.cs_call_center_sk, catalog_sales.cs_catalog_page_sk, catalog_sales.cs_ship_mode_sk, catalog_sales.cs_warehouse_sk, catalog_sales.cs_item_sk, catalog_sales.cs_promo_sk, catalog_sales.cs_order_number, catalog_sales.cs_quantity, catalog_sales.cs_wholesale_cost, catalog_sales.cs_list_price, catalog_sales.cs_sales_price, catalog_sales.cs_ext_discount_amt, catalog_sales.cs_ext_sales_price, catalog_sales.cs_ext_wholesale_cost, catalog_sales.cs_ext_list_price, catalog_sales.cs_ext_tax, catalog_sales.cs_coupon_amt, catalog_sales.cs_ext_ship_cost, catalog_sales.cs_net_paid, catalog_sales.cs_net_paid_inc_tax, catalog_sales.cs_net_paid_inc_ship, catalog_sales.cs_net_paid_inc_ship_tax, catalog_sales.cs_net_profit Index Cond: (catalog_sales.cs_sold_date_sk = date_dim_1.d_date_sk) Worker 0: actual time=0.004..0.315 rows=1538 loops=175 -> Index Scan using customer_pkey on public.customer customer_1 (cost=0.42..0.44 rows=1 width=17) (actual time=0.001..0.001 rows=1 loops=570105) Output: customer_1.c_customer_sk, customer_1.c_customer_id, customer_1.c_current_cdemo_sk, customer_1.c_current_hdemo_sk, customer_1.c_current_addr_sk, customer_1.c_first_shipto_date_sk, customer_1.c_first_sales_date_sk, customer_1.c_salutation, customer_1.c_first_name, customer_1.c_last_name, customer_1.c_preferred_cust_flag, customer_1.c_birth_day, customer_1.c_birth_month, customer_1.c_birth_year, customer_1.c_birth_country, customer_1.c_login, customer_1.c_email_address, customer_1.c_last_review_date_sk Index Cond: (customer_1.c_customer_sk = catalog_sales.cs_bill_customer_sk) Worker 0: actual time=0.001..0.001 rows=1 loops=269122 -> Subquery Scan on "*SELECT* 3" (cost=50558.61..51486.87 rows=6929 width=21) (actual time=308.103..403.898 rows=23737 loops=1) Output: "*SELECT* 3".c_last_name, "*SELECT* 3".c_first_name, "*SELECT* 3".d_date, 1 -> Unique (cost=50558.61..51417.58 rows=6929 width=17) (actual time=308.101..401.424 rows=23737 loops=1) Output: customer_2.c_last_name, customer_2.c_first_name, date_dim_2.d_date -> Gather Merge (cost=50558.61..51365.61 rows=6929 width=17) (actual time=308.100..370.805 rows=287761 loops=1) Output: customer_2.c_last_name, customer_2.c_first_name, date_dim_2.d_date Workers Planned: 2 Workers Launched: 2 -> Sort (cost=49558.59..49565.81 rows=2887 width=17) (actual time=304.699..310.446 rows=95920 loops=3) Output: customer_2.c_last_name, customer_2.c_first_name, date_dim_2.d_date Sort Key: customer_2.c_last_name, customer_2.c_first_name, date_dim_2.d_date Sort Method: quicksort Memory: 7444kB Worker 0: actual time=304.767..309.513 rows=96821 loops=1 Sort Method: quicksort Memory: 7379kB Worker 1: actual time=301.512..309.827 rows=92877 loops=1 Sort Method: quicksort Memory: 7205kB -> Nested Loop (cost=2570.55..49392.65 rows=2887 width=17) (actual time=3.419..230.307 rows=95920 loops=3) Output: customer_2.c_last_name, customer_2.c_first_name, date_dim_2.d_date Inner Unique: true Worker 0: actual time=2.440..229.401 rows=96821 loops=1 Worker 1: actual time=2.704..230.216 rows=92877 loops=1 -> Parallel Hash Join (cost=2570.12..48102.45 rows=2887 width=8) (actual time=3.401..99.460 rows=95936 loops=3) Output: web_sales.ws_bill_customer_sk, date_dim_2.d_date Inner Unique: true Hash Cond: (web_sales.ws_sold_date_sk = date_dim_2.d_date_sk) Worker 0: actual time=2.417..97.885 rows=96841 loops=1 Worker 1: actual time=2.685..97.490 rows=92890 loops=1 -> Parallel Seq Scan on public.web_sales (cost=0.00..43955.11 rows=600811 width=8) (actual time=0.016..53.317 rows=480649 loops=3) Output: web_sales.ws_sold_date_sk, web_sales.ws_sold_time_sk, web_sales.ws_ship_date_sk, web_sales.ws_item_sk, web_sales.ws_bill_customer_sk, web_sales.ws_bill_cdemo_sk, web_sales.ws_bill_hdemo_sk, web_sales.ws_bill_addr_sk, web_sales.ws_ship_customer_sk, web_sales.ws_ship_cdemo_sk, web_sales.ws_ship_hdemo_sk, web_sales.ws_ship_addr_sk, web_sales.ws_web_page_sk, web_sales.ws_web_site_sk, web_sales.ws_ship_mode_sk, web_sales.ws_warehouse_sk, web_sales.ws_promo_sk, web_sales.ws_order_number, web_sales.ws_quantity, web_sales.ws_wholesale_cost, web_sales.ws_list_price, web_sales.ws_sales_price, web_sales.ws_ext_discount_amt, web_sales.ws_ext_sales_price, web_sales.ws_ext_wholesale_cost, web_sales.ws_ext_list_price, web_sales.ws_ext_tax, web_sales.ws_coupon_amt, web_sales.ws_ext_ship_cost, web_sales.ws_net_paid, web_sales.ws_net_paid_inc_tax, web_sales.ws_net_paid_inc_ship, web_sales.ws_net_paid_inc_ship_tax, web_sales.ws_net_profit Worker 0: actual time=0.019..52.418 rows=484786 loops=1 Worker 1: actual time=0.020..53.691 rows=460693 loops=1 -> Parallel Hash (cost=2567.55..2567.55 rows=206 width=8) (actual time=3.208..3.209 rows=122 loops=3) Output: date_dim_2.d_date, date_dim_2.d_date_sk Buckets: 1024 Batches: 1 Memory Usage: 104kB Worker 0: actual time=2.256..2.257 rows=152 loops=1 Worker 1: actual time=2.557..2.558 rows=76 loops=1 -> Parallel Seq Scan on public.date_dim date_dim_2 (cost=0.00..2567.55 rows=206 width=8) (actual time=1.291..3.153 rows=122 loops=3) Output: date_dim_2.d_date, date_dim_2.d_date_sk Filter: ((date_dim_2.d_month_seq >= 1176) AND (date_dim_2.d_month_seq <= 1187)) Rows Removed by Filter: 24228 Worker 0: actual time=0.329..2.197 rows=152 loops=1 Worker 1: actual time=0.643..2.497 rows=76 loops=1 -> Index Scan using customer_pkey on public.customer customer_2 (cost=0.42..0.45 rows=1 width=17) (actual time=0.001..0.001 rows=1 loops=287809) Output: customer_2.c_customer_sk, customer_2.c_customer_id, customer_2.c_current_cdemo_sk, customer_2.c_current_hdemo_sk, customer_2.c_current_addr_sk, customer_2.c_first_shipto_date_sk, customer_2.c_first_sales_date_sk, customer_2.c_salutation, customer_2.c_first_name, customer_2.c_last_name, customer_2.c_preferred_cust_flag, customer_2.c_birth_day, customer_2.c_birth_month, customer_2.c_birth_year, customer_2.c_birth_country, customer_2.c_login, customer_2.c_email_address, customer_2.c_last_review_date_sk Index Cond: (customer_2.c_customer_sk = web_sales.ws_bill_customer_sk) Worker 0: actual time=0.001..0.001 rows=1 loops=96841 Worker 1: actual time=0.001..0.001 rows=1 loops=92890 Planning Time: 3.013 ms Execution Time: 2203.990 ms (145 rows) ^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: benchmark results comparing versions 15.2 and 16 @ 2023-07-07 13:00 Alexander Lakhin <[email protected]> parent: Alexander Lakhin <[email protected]> 0 siblings, 0 replies; 8+ messages in thread From: Alexander Lakhin @ 2023-07-07 13:00 UTC (permalink / raw) To: David Rowley <[email protected]>; +Cc: MARK CALLAGHAN <[email protected]>; [email protected]; Andres Freund <[email protected]> Hello David, 11.05.2023 16:00, Alexander Lakhin wrote: > Yeah, I see. It's also interesting to me, which tests perform better after > that commit. It takes several hours to run all tests, so I can't present > results quickly, but I'll try to collect this information next week. To my regret, I could not find such tests that week, so I decided to try later, after reviewing my benchmark running infrastructure. But for now, as Postgres Pro company graciously shared the benchmarking infrastructure project [1], it's possible for anyone to confirm or deny my results. (You can also see which tests were performed and how.) Having done one more round of comprehensive testing, I still couldn't find winning tests for commit 3c6fc5820 (but reassured that test s64da_tpcds.query87 loses a little (and also s64da_tpcds.query38)). So it seems to me that the tests I performed or their parameters is not representative enough for that improvement, unfortunately. [1] https://github.com/postgrespro/pg-mark Best regards, Alexander ^ permalink raw reply [nested|flat] 8+ messages in thread
end of thread, other threads:[~2023-07-07 13:00 UTC | newest] Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2020-11-20 02:48 [PATCH 1/3] bootstrap: convert Typ to a List* Justin Pryzby <[email protected]> 2023-05-05 17:45 benchmark results comparing versions 15.2 and 16 MARK CALLAGHAN <[email protected]> 2023-05-05 20:34 ` Re: benchmark results comparing versions 15.2 and 16 Andres Freund <[email protected]> 2023-05-08 13:00 ` Re: benchmark results comparing versions 15.2 and 16 Alexander Lakhin <[email protected]> 2023-05-10 13:00 ` Re: benchmark results comparing versions 15.2 and 16 Alexander Lakhin <[email protected]> 2023-05-10 22:27 ` Re: benchmark results comparing versions 15.2 and 16 David Rowley <[email protected]> 2023-05-11 13:00 ` Re: benchmark results comparing versions 15.2 and 16 Alexander Lakhin <[email protected]> 2023-07-07 13:00 ` Re: benchmark results comparing versions 15.2 and 16 Alexander Lakhin <[email protected]>
This inbox is served by agora; see mirroring instructions for how to clone and mirror all data and code used for this inbox