explain.depesz.com

PostgreSQL's explain analyze made readable

Result: EUPs : Remade amplitude companies question

Settings
# exclusive inclusive rows x rows loops node
1. 108.701 1,250,166.411 ↓ 404.3 85,300 1

Nested Loop (cost=17,079,243.85..17,081,097.32 rows=211 width=508) (actual time=1,249,174.646..1,250,166.411 rows=85,300 loops=1)

2.          

CTE first_c

3. 536.786 420,625.914 ↓ 2.0 83,677 1

Subquery Scan on events_c (cost=7,990,492.24..8,827,161.34 rows=41,833 width=238) (actual time=355,070.665..420,625.914 rows=83,677 loops=1)

  • Filter: (events_c.rn = 1)
  • Rows Removed by Filter: 1856798
4. 63,434.634 420,089.128 ↑ 4.3 1,940,475 1

WindowAgg (cost=7,990,492.24..8,722,577.70 rows=8,366,691 width=270) (actual time=355,070.663..420,089.128 rows=1,940,475 loops=1)

5. 163,275.950 356,654.494 ↑ 4.3 1,940,475 1

Sort (cost=7,990,492.24..8,011,408.97 rows=8,366,691 width=507) (actual time=355,070.569..356,654.494 rows=1,940,475 loops=1)

  • Sort Key: ((fact_amplitude_events.event_properties ->> 'company_id'::text)), (timezone('UTC'::text, fact_amplitude_events.client_event_time))
  • Sort Method: external merge Disk: 994000kB
6. 193,378.544 193,378.544 ↑ 4.3 1,940,475 1

Seq Scan on fact_amplitude_events (cost=0.00..1,280,465.67 rows=8,366,691 width=507) (actual time=0.938..193,378.544 rows=1,940,475 loops=1)

  • Filter: (CASE WHEN ((event_properties ->> 'company_id'::text) ~ '^\d+$'::text) THEN ((event_properties ->> 'company_id'::text))::integer WHEN ((event_properties ->> 'company_id'::text) !~ '^\d+$'::text) THEN NULL::integer ELSE NULL::integer END IS NOT NULL)
  • Rows Removed by Filter: 6477990
7.          

CTE add_users

8. 714.092 423,839.764 ↓ 2.0 83,677 1

Hash Left Join (cost=98,787.17..107,104.30 rows=41,833 width=516) (actual time=356,623.844..423,839.764 rows=83,677 loops=1)

  • Hash Cond: (first_c.company_key = foo.company_key)
9. 421,574.428 421,574.428 ↓ 2.0 83,677 1

CTE Scan on first_c (cost=0.00..836.66 rows=41,833 width=512) (actual time=355,070.668..421,574.428 rows=83,677 loops=1)

10. 49.030 1,551.244 ↓ 65.1 153,555 1

Hash (cost=98,757.69..98,757.69 rows=2,358 width=8) (actual time=1,551.243..1,551.244 rows=153,555 loops=1)

  • Buckets: 131072 (originally 4096) Batches: 2 (originally 1) Memory Usage: 4023kB
11. 68.295 1,502.214 ↓ 65.1 153,555 1

Subquery Scan on foo (cost=83,432.16..98,757.69 rows=2,358 width=8) (actual time=1,262.020..1,502.214 rows=153,555 loops=1)

  • Filter: (foo.rk = 1)
  • Rows Removed by Filter: 8014
12. 125.507 1,433.919 ↑ 2.9 161,569 1

WindowAgg (cost=83,432.16..92,863.26 rows=471,555 width=24) (actual time=1,262.018..1,433.919 rows=161,569 loops=1)

13. 860.727 1,308.412 ↑ 2.9 161,569 1

Sort (cost=83,432.16..84,611.04 rows=471,555 width=16) (actual time=1,262.012..1,308.412 rows=161,569 loops=1)

  • Sort Key: fcur.company_key, fcur.fcur_created_at
  • Sort Method: external merge Disk: 4752kB
14. 127.741 447.685 ↑ 2.9 161,569 1

Hash Join (cost=13,327.08..30,934.51 rows=471,555 width=16) (actual time=270.099..447.685 rows=161,569 loops=1)

  • Hash Cond: (fcur.user_key = du.user_key)
15. 50.936 50.936 ↑ 3.5 168,599 1

Seq Scan on fact_company_user_roles fcur (cost=0.00..8,804.74 rows=597,974 width=16) (actual time=0.088..50.936 rows=168,599 loops=1)

16. 211.488 269.008 ↑ 2.2 159,204 1

Hash (cost=7,478.28..7,478.28 rows=356,464 width=4) (actual time=269.007..269.008 rows=159,204 loops=1)

  • Buckets: 131072 Batches: 8 Memory Usage: 1732kB
17. 57.520 57.520 ↑ 2.2 159,204 1

Seq Scan on dim_users du (cost=0.00..7,478.28 rows=356,464 width=4) (actual time=0.037..57.520 rows=159,204 loops=1)

  • Filter: (NOT is_agent)
  • Rows Removed by Filter: 176
18.          

CTE first_u

19. 603.045 822,427.070 ↑ 2.2 19,265 1

Subquery Scan on events_u (cost=7,263,824.22..8,016,826.41 rows=41,833 width=238) (actual time=713,095.816..822,427.070 rows=19,265 loops=1)

  • Filter: (events_u.rn = 1)
  • Rows Removed by Filter: 2169782
20. 91,014.070 821,824.025 ↑ 3.8 2,189,047 1

WindowAgg (cost=7,263,824.22..7,912,242.77 rows=8,366,691 width=270) (actual time=713,095.813..821,824.025 rows=2,189,047 loops=1)

21. 181,814.969 730,809.955 ↑ 3.8 2,189,047 1

Sort (cost=7,263,824.22..7,284,740.94 rows=8,366,691 width=456) (actual time=713,095.714..730,809.955 rows=2,189,047 loops=1)

  • Sort Key: ((fact_amplitude_events_1.user_properties ->> 'user_id'::text)), (timezone('UTC'::text, fact_amplitude_events_1.client_event_time))
  • Sort Method: external merge Disk: 1189096kB
22. 548,994.986 548,994.986 ↑ 3.8 2,189,047 1

Seq Scan on fact_amplitude_events fact_amplitude_events_1 (cost=0.00..1,154,334.64 rows=8,366,691 width=456) (actual time=0.022..548,994.986 rows=2,189,047 loops=1)

  • Filter: ((user_properties ->> 'user_id'::text) IS NOT NULL)
  • Rows Removed by Filter: 6229418
23.          

CTE add_comps

24. 46.734 823,965.271 ↓ 69.5 19,247 1

Merge Join (cost=112,520.62..112,744.34 rows=277 width=516) (actual time=823,873.456..823,965.271 rows=19,247 loops=1)

  • Merge Cond: (first_u.user_key = foo_1.user_key)
25. 418.704 822,982.800 ↑ 2.2 19,265 1

Sort (cost=13,630.84..13,735.42 rows=41,833 width=512) (actual time=822,976.625..822,982.800 rows=19,265 loops=1)

  • Sort Key: first_u.user_key
  • Sort Method: external merge Disk: 3176kB
26. 822,564.096 822,564.096 ↑ 2.2 19,265 1

CTE Scan on first_u (cost=0.00..836.66 rows=41,833 width=512) (actual time=713,095.820..822,564.096 rows=19,265 loops=1)

27. 167.408 935.737 ↓ 65.1 153,402 1

Sort (cost=98,889.78..98,895.68 rows=2,358 width=8) (actual time=896.819..935.737 rows=153,402 loops=1)

  • Sort Key: foo_1.user_key
  • Sort Method: external sort Disk: 3312kB
28. 70.368 768.329 ↓ 65.1 153,555 1

Subquery Scan on foo_1 (cost=83,432.16..98,757.69 rows=2,358 width=8) (actual time=520.139..768.329 rows=153,555 loops=1)

  • Filter: (foo_1.rk = 1)
  • Rows Removed by Filter: 8014
29. 129.941 697.961 ↑ 2.9 161,569 1

WindowAgg (cost=83,432.16..92,863.26 rows=471,555 width=24) (actual time=520.138..697.961 rows=161,569 loops=1)

30. 142.938 568.020 ↑ 2.9 161,569 1

Sort (cost=83,432.16..84,611.04 rows=471,555 width=16) (actual time=520.132..568.020 rows=161,569 loops=1)

  • Sort Key: fcur_1.company_key, fcur_1.fcur_created_at
  • Sort Method: external merge Disk: 4752kB
31. 207.998 425.082 ↑ 2.9 161,569 1

Hash Join (cost=13,327.08..30,934.51 rows=471,555 width=16) (actual time=208.812..425.082 rows=161,569 loops=1)

  • Hash Cond: (fcur_1.user_key = du_1.user_key)
32. 51.158 51.158 ↑ 3.5 168,599 1

Seq Scan on fact_company_user_roles fcur_1 (cost=0.00..8,804.74 rows=597,974 width=16) (actual time=0.101..51.158 rows=168,599 loops=1)

33. 108.568 165.926 ↑ 2.2 159,204 1

Hash (cost=7,478.28..7,478.28 rows=356,464 width=4) (actual time=165.926..165.926 rows=159,204 loops=1)

  • Buckets: 131072 Batches: 8 Memory Usage: 1732kB
34. 57.358 57.358 ↑ 2.2 159,204 1

Seq Scan on dim_users du_1 (cost=0.00..7,478.28 rows=356,464 width=4) (actual time=0.029..57.358 rows=159,204 loops=1)

  • Filter: (NOT is_agent)
  • Rows Removed by Filter: 176
35.          

CTE together

36. 42.986 1,248,025.263 ↓ 2.4 102,924 1

Append (cost=0.00..842.20 rows=42,110 width=508) (actual time=356,623.847..1,248,025.263 rows=102,924 loops=1)

37. 423,991.725 423,991.725 ↓ 2.0 83,677 1

CTE Scan on add_users (cost=0.00..836.66 rows=41,833 width=508) (actual time=356,623.846..423,991.725 rows=83,677 loops=1)

38. 823,990.552 823,990.552 ↓ 69.5 19,247 1

CTE Scan on add_comps (cost=0.00..5.54 rows=277 width=508) (actual time=823,873.461..823,990.552 rows=19,247 loops=1)

39.          

CTE first_t

40. 104.459 1,249,834.473 ↓ 2.4 102,924 1

WindowAgg (cost=13,722.64..14,564.84 rows=42,110 width=516) (actual time=1,249,174.600..1,249,834.473 rows=102,924 loops=1)

41. 1,557.500 1,249,730.014 ↓ 2.4 102,924 1

Sort (cost=13,722.64..13,827.92 rows=42,110 width=508) (actual time=1,249,174.591..1,249,730.014 rows=102,924 loops=1)

  • Sort Key: together.company_key, together.client_event_time
  • Sort Method: external merge Disk: 17800kB
42. 1,248,172.514 1,248,172.514 ↓ 2.4 102,924 1

CTE Scan on together (cost=0.00..842.20 rows=42,110 width=508) (actual time=356,623.849..1,248,172.514 rows=102,924 loops=1)

43. 1,249,972.395 1,249,972.395 ↓ 404.3 85,315 1

CTE Scan on first_t ampl (cost=0.00..947.47 rows=211 width=508) (actual time=1,249,174.605..1,249,972.395 rows=85,315 loops=1)

  • Filter: (rn2 = 1)
  • Rows Removed by Filter: 17609
44. 85.315 85.315 ↑ 1.0 1 85,315

Index Only Scan using dim_companies_pkey on dim_companies dc (cost=0.42..4.29 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=85,315)

  • Index Cond: (company_key = ampl.company_key)
  • Heap Fetches: 0
Planning time : 2.181 ms
Execution time : 1,250,793.705 ms