Skip to main content

Return to Question

Notice removed Canonical answer required by CommunityBot
Bounty Ended with no winning answer by CommunityBot
trim more noise that's not helping the case
Source Link
Erwin Brandstetter
Erwin Brandstetter
  • 668.4k
  • 159
  • 1.2k
  • 1.3k

Partial index on timestamp unused by the query planner despite overlappingmatching period in WHERE conditionclause

CREATE TABLE table1 (
    key1 integer,
    key2 character varying(128),
    key3 character varying(64),
    key4 character varying(128),
    key5 character varying(10),
    key6 timestamp with time zone,
    key7 timestamp with time zone,
    key8 integer NOT NULL, GENERATED BY DEFAULT AS IDENTITY
    key9 boolean NOT NULL,
    key10 timestamp with time zone
);

ALTER TABLE table1 ALTER COLUMN key8 ADD GENERATED BY DEFAULT AS IDENTITY (
    SEQUENCE NAME table1_key8_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1
);

CREATE TABLE table2 (
    key2 integer NOT NULL, GENERATED BY DEFAULT AS IDENTITY
    key3 timestamp with time zone NOT NULL,
    key1 integer
);

ALTER TABLE table2 ALTER COLUMN key2 ADD GENERATED BY DEFAULT AS IDENTITY (
    SEQUENCE NAME table2_key2_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1
);

CREATE TABLE table3 (
    key1 character varying(128) NOT NULL,
    key2 text
);

  
CREATE TABLE table4 (
    key1 character varying(64) NOT NULL,
    key2 text
);

SELECT table1.key1 AS "a"a,
       table1.key2 AS "b"b,
       table1.key3 AS "c"c,
       table1.key4 AS "d"d,
       table2.key1 AS "e"e,
       COUNT(DISTINCT table1.key8) FILTER (WHERE table1.key5 = 'A') AS "f"f,
       COUNT(DISTINCT table1.key8) FILTER (WHERE table1.key5 = 'B') AS "g"g,
       COUNT(DISTINCT table1.key8) FILTER (WHERE table1.key5 = 'C') AS "h"h,
       COUNT(DISTINCT table1.key8) FILTER (WHERE table1.key5 = 'D') AS "i"i,
       COUNT(DISTINCT table1.key8) FILTER (WHERE table1.key5 IS NULL) AS "j"j,
       SUM((table1.key6 - table1.key7)) AS "k"k,
       COUNT(DISTINCT table1.key8) FILTER (WHERE table1.key9) AS "l"l,
       COUNT(DISTINCT table1.key8) AS "m"m
FROM table1
LEFT OUTER JOIN table3 ON (table1.key4 = table3.key1)
LEFT OUTER JOIN table2 ON (table1.key1 = table2.key2)
LEFT OUTER JOIN table4 ON (table1.key3 = table4.key1)
WHERE table1.key10 BETWEEN '2023-10-31 23:00:00+00:00' AND '2023-11-02 23:00:00+00:00'
GROUP BY 1,
         2,
         3,
         4,
         5,
         table2.key3,
         table4.key2,
         table3.key2
ORDER BY table2.key3 DESC,
         table1.key2 ASC,
         table4.key2 DESC,
         table3.key2 DESC,
         table2.key1 ASC
LIMIT 20

Partial index on timestamp unused by the query planner despite overlapping period in WHERE condition

CREATE TABLE table1 (
    key1 integer,
    key2 character varying(128),
    key3 character varying(64),
    key4 character varying(128),
    key5 character varying(10),
    key6 timestamp with time zone,
    key7 timestamp with time zone,
    key8 integer NOT NULL,
    key9 boolean NOT NULL,
    key10 timestamp with time zone
);

ALTER TABLE table1 ALTER COLUMN key8 ADD GENERATED BY DEFAULT AS IDENTITY (
    SEQUENCE NAME table1_key8_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1
);

CREATE TABLE table2 (
    key2 integer NOT NULL,
    key3 timestamp with time zone NOT NULL,
    key1 integer
);

ALTER TABLE table2 ALTER COLUMN key2 ADD GENERATED BY DEFAULT AS IDENTITY (
    SEQUENCE NAME table2_key2_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1
);

CREATE TABLE table3 (
    key1 character varying(128) NOT NULL,
    key2 text
);

 CREATE TABLE table4 (
    key1 character varying(64) NOT NULL,
    key2 text
);

SELECT table1.key1 AS "a",
       table1.key2 AS "b",
       table1.key3 AS "c",
       table1.key4 AS "d",
       table2.key1 AS "e",
       COUNT(DISTINCT table1.key8) FILTER (WHERE table1.key5 = 'A') AS "f",
       COUNT(DISTINCT table1.key8) FILTER (WHERE table1.key5 = 'B') AS "g",
       COUNT(DISTINCT table1.key8) FILTER (WHERE table1.key5 = 'C') AS "h",
       COUNT(DISTINCT table1.key8) FILTER (WHERE table1.key5 = 'D') AS "i",
       COUNT(DISTINCT table1.key8) FILTER (WHERE table1.key5 IS NULL) AS "j",
       SUM((table1.key6 - table1.key7)) AS "k",
       COUNT(DISTINCT table1.key8) FILTER (WHERE table1.key9) AS "l",
       COUNT(DISTINCT table1.key8) AS "m"
FROM table1
LEFT OUTER JOIN table3 ON (table1.key4 = table3.key1)
LEFT OUTER JOIN table2 ON (table1.key1 = table2.key2)
LEFT OUTER JOIN table4 ON (table1.key3 = table4.key1)
WHERE table1.key10 BETWEEN '2023-10-31 23:00:00+00:00' AND '2023-11-02 23:00:00+00:00'
GROUP BY 1,
         2,
         3,
         4,
         5,
         table2.key3,
         table4.key2,
         table3.key2
ORDER BY table2.key3 DESC,
         table1.key2 ASC,
         table4.key2 DESC,
         table3.key2 DESC,
         table2.key1 ASC
LIMIT 20

Partial index on timestamp unused by the query planner despite matching period in WHERE clause

CREATE TABLE table1 (
    key1 integer,
    key2 character varying(128),
    key3 character varying(64),
    key4 character varying(128),
    key5 character varying(10),
    key6 timestamp with time zone,
    key7 timestamp with time zone,
    key8 integer NOT NULL GENERATED BY DEFAULT AS IDENTITY
    key9 boolean NOT NULL,
    key10 timestamp with time zone
);

CREATE TABLE table2 (
    key2 integer NOT NULL GENERATED BY DEFAULT AS IDENTITY
    key3 timestamp with time zone NOT NULL,
    key1 integer
);

CREATE TABLE table3 (
    key1 character varying(128) NOT NULL,
    key2 text
);
 
CREATE TABLE table4 (
    key1 character varying(64) NOT NULL,
    key2 text
);

SELECT table1.key1 AS a,
       table1.key2 AS b,
       table1.key3 AS c,
       table1.key4 AS d,
       table2.key1 AS e,
       COUNT(DISTINCT table1.key8) FILTER (WHERE table1.key5 = 'A') AS f,
       COUNT(DISTINCT table1.key8) FILTER (WHERE table1.key5 = 'B') AS g,
       COUNT(DISTINCT table1.key8) FILTER (WHERE table1.key5 = 'C') AS h,
       COUNT(DISTINCT table1.key8) FILTER (WHERE table1.key5 = 'D') AS i,
       COUNT(DISTINCT table1.key8) FILTER (WHERE table1.key5 IS NULL) AS j,
       SUM((table1.key6 - table1.key7)) AS k,
       COUNT(DISTINCT table1.key8) FILTER (WHERE table1.key9) AS l,
       COUNT(DISTINCT table1.key8) AS m
FROM table1
LEFT OUTER JOIN table3 ON (table1.key4 = table3.key1)
LEFT OUTER JOIN table2 ON (table1.key1 = table2.key2)
LEFT OUTER JOIN table4 ON (table1.key3 = table4.key1)
WHERE table1.key10 BETWEEN '2023-10-31 23:00:00+00:00' AND '2023-11-02 23:00:00+00:00'
GROUP BY 1,
         2,
         3,
         4,
         5,
         table2.key3,
         table4.key2,
         table3.key2
ORDER BY table2.key3 DESC,
         table1.key2 ASC,
         table4.key2 DESC,
         table3.key2 DESC,
         table2.key1 ASC
LIMIT 20
plain format is best for EXPLAIN output
Source Link
Erwin Brandstetter
Erwin Brandstetter
  • 668.4k
  • 159
  • 1.2k
  • 1.3k

Query ExplainOutput of EXPLAIN ANALYZE:

QUERY PLAN
Limit (cost=18341692.59..18341693.89 rows=20 width=159) (actual time=32149.681..32192.245 rows=20 loops=1)
-> GroupAggregate (cost=18341692.59..18353658.57 rows=184092 width=159) (actual time=31749.816..31792.379 rows=20 loops=1)
Group Key: table2.key3, table1.key2, table4.key2, table3.key2, table2.key1, table1.key1, table1.key3, table1.key4
-> Sort (cost=18341692.59..18342152.82 rows=184092 width=115) (actual time=31749.763..31792.253 rows=80 loops=1)
Sort Key: table2.key3 DESC, table1.key2, table4.key2 DESC, table3.key2 DESC, table2.key1, table1.key1, table1.key3, table1.key4
Sort Method: external merge Disk: 23424kB
-> Gather (cost=83595.20..18314267.69 rows=184092 width=115) (actual time=31417.800..31681.660 rows=186540 loops=1)
Workers Planned: 2
Workers Launched: 2
-> Hash Left Join (cost=82595.20..18294858.49 rows=76705 width=115) (actual time=31392.569..31545.627 rows=62180 loops=3)
Hash Cond: ((table1.key4)::text = (table3.key1)::text)
-> Hash Left Join (cost=81113.79..18293175.70 rows=76705 width=90) (actual time=31379.926..31524.528 rows=62180 loops=3)
Hash Cond: ((table1.key3)::text = (table4.key1)::text)
-> Parallel Hash Left Join (cost=78481.53..18288053.08 rows=76705 width=72) (actual time=31361.889..31481.346 rows=62180 loops=3)
Hash Cond: (table1.key1 = table2.key2)
-> Parallel Seq Scan on table1 (cost=0.00..18200395.20 rows=76705 width=60) (actual time=2.888..30824.027 rows=62180 loops=3)
Filter: ((key10 >= '2023-10-31 23:00:00+00'::timestamp with time zone) AND (key10 "<= '2023-11-02 23:00:00+00'::timestamp with time zone))
Rows Removed by Filter: 43882417
-> Parallel Hash (cost=52397.57..52397.57 rows=1500557 width=16) (actual time=497.607..497.608 rows=1200445 loops=3)
Buckets: 131072 Batches: 64 Memory Usage: 3712kB
-> Parallel Seq Scan on table2 (cost=0.00..52397.57 rows=1500557 width=16) (actual time=225.666..349.851 rows=1200445 loops=3)
-> Hash (cost=1244.45..1244.45 rows=71745 width=25) (actual time=17.720..17.721 rows=72031 loops=3)
Buckets: 65536 Batches: 2 Memory Usage: 2568kB
-> Seq Scan on table4 (cost=0.00..1244.45 rows=71745 width=25) (actual time=0.019..6.313 rows=72031 loops=3)
-> Hash (cost=893.96..893.96 rows=46996 width=40) (actual time=12.367..12.368 rows=47030 loops=3)
Buckets: 65536 Batches: 1 Memory Usage: 3865kB
-> Seq Scan on table3 (cost=0.00..893.96 rows=46996 width=40) (actual time=0.017..4.731 rows=47030 loops=3)
Planning Time: 2.249 ms
JIT:
Functions: 90
Options: Inlining true, Optimization true, Expressions true, Deforming true
Timing: Generation 8.216 ms, Inlining 84.266 ms, Optimization 596.840 ms, Emission 394.586 ms, Total 1083.909 ms
Execution Time: 32199.853 ms
Limit  (cost=18341692.59..18341693.89 rows=20 width=159) (actual time=32149.681..32192.245 rows=20 loops=1)
  ->  GroupAggregate  (cost=18341692.59..18353658.57 rows=184092 width=159) (actual time=31749.816..31792.379 rows=20 loops=1)
        Group Key: table2.key3, table1.key2, table4.key2, table3.key2, table2.key1, table1.key1, table1.key3, table1.key4
        ->  Sort  (cost=18341692.59..18342152.82 rows=184092 width=115) (actual time=31749.763..31792.253 rows=80 loops=1)
              Sort Key: table2.key3 DESC, table1.key2, table4.key2 DESC, table3.key2 DESC, table2.key1, table1.key1, table1.key3, table1.key4
              Sort Method: external merge  Disk: 23424kB
              ->  Gather  (cost=83595.20..18314267.69 rows=184092 width=115) (actual time=31417.800..31681.660 rows=186540 loops=1)
                    Workers Planned: 2
                    Workers Launched: 2
                    ->  Hash Left Join  (cost=82595.20..18294858.49 rows=76705 width=115) (actual time=31392.569..31545.627 rows=62180 loops=3)
                          Hash Cond: ((table1.key4)::text = (table3.key1)::text)
                          ->  Hash Left Join  (cost=81113.79..18293175.70 rows=76705 width=90) (actual time=31379.926..31524.528 rows=62180 loops=3)
                                Hash Cond: ((table1.key3)::text = (table4.key1)::text)
                                ->  Parallel Hash Left Join  (cost=78481.53..18288053.08 rows=76705 width=72) (actual time=31361.889..31481.346 rows=62180 loops=3)
                                      Hash Cond: (table1.key1 = table2.key2)
                                      ->  Parallel Seq Scan on table1  (cost=0.00..18200395.20 rows=76705 width=60) (actual time=2.888..30824.027 rows=62180 loops=3)
                                            Filter: ((key10 >= '2023-10-31 23:00:00+00'::timestamp with time zone) AND (key10 "<= '2023-11-02 23:00:00+00'::timestamp with time zone))
                                            Rows Removed by Filter: 43882417
                                      ->  Parallel Hash  (cost=52397.57..52397.57 rows=1500557 width=16) (actual time=497.607..497.608 rows=1200445 loops=3)
                                            Buckets: 131072  Batches: 64  Memory Usage: 3712kB
                                            ->  Parallel Seq Scan on table2  (cost=0.00..52397.57 rows=1500557 width=16) (actual time=225.666..349.851 rows=1200445 loops=3)
                                ->  Hash  (cost=1244.45..1244.45 rows=71745 width=25) (actual time=17.720..17.721 rows=72031 loops=3)
                                      Buckets: 65536  Batches: 2  Memory Usage: 2568kB
                                      ->  Seq Scan on table4  (cost=0.00..1244.45 rows=71745 width=25) (actual time=0.019..6.313 rows=72031 loops=3)
                          ->  Hash  (cost=893.96..893.96 rows=46996 width=40) (actual time=12.367..12.368 rows=47030 loops=3)
                                Buckets: 65536  Batches: 1  Memory Usage: 3865kB
                                ->  Seq Scan on table3  (cost=0.00..893.96 rows=46996 width=40) (actual time=0.017..4.731 rows=47030 loops=3)
Planning Time: 2.249 ms
JIT:
  Functions: 90
  Options: Inlining true, Optimization true, Expressions true, Deforming true
  Timing: Generation 8.216 ms, Inlining 84.266 ms, Optimization 596.840 ms, Emission 394.586 ms, Total 1083.909 ms
Execution Time: 32199.853 ms

Query Explain:

QUERY PLAN
Limit (cost=18341692.59..18341693.89 rows=20 width=159) (actual time=32149.681..32192.245 rows=20 loops=1)
-> GroupAggregate (cost=18341692.59..18353658.57 rows=184092 width=159) (actual time=31749.816..31792.379 rows=20 loops=1)
Group Key: table2.key3, table1.key2, table4.key2, table3.key2, table2.key1, table1.key1, table1.key3, table1.key4
-> Sort (cost=18341692.59..18342152.82 rows=184092 width=115) (actual time=31749.763..31792.253 rows=80 loops=1)
Sort Key: table2.key3 DESC, table1.key2, table4.key2 DESC, table3.key2 DESC, table2.key1, table1.key1, table1.key3, table1.key4
Sort Method: external merge Disk: 23424kB
-> Gather (cost=83595.20..18314267.69 rows=184092 width=115) (actual time=31417.800..31681.660 rows=186540 loops=1)
Workers Planned: 2
Workers Launched: 2
-> Hash Left Join (cost=82595.20..18294858.49 rows=76705 width=115) (actual time=31392.569..31545.627 rows=62180 loops=3)
Hash Cond: ((table1.key4)::text = (table3.key1)::text)
-> Hash Left Join (cost=81113.79..18293175.70 rows=76705 width=90) (actual time=31379.926..31524.528 rows=62180 loops=3)
Hash Cond: ((table1.key3)::text = (table4.key1)::text)
-> Parallel Hash Left Join (cost=78481.53..18288053.08 rows=76705 width=72) (actual time=31361.889..31481.346 rows=62180 loops=3)
Hash Cond: (table1.key1 = table2.key2)
-> Parallel Seq Scan on table1 (cost=0.00..18200395.20 rows=76705 width=60) (actual time=2.888..30824.027 rows=62180 loops=3)
Filter: ((key10 >= '2023-10-31 23:00:00+00'::timestamp with time zone) AND (key10 "<= '2023-11-02 23:00:00+00'::timestamp with time zone))
Rows Removed by Filter: 43882417
-> Parallel Hash (cost=52397.57..52397.57 rows=1500557 width=16) (actual time=497.607..497.608 rows=1200445 loops=3)
Buckets: 131072 Batches: 64 Memory Usage: 3712kB
-> Parallel Seq Scan on table2 (cost=0.00..52397.57 rows=1500557 width=16) (actual time=225.666..349.851 rows=1200445 loops=3)
-> Hash (cost=1244.45..1244.45 rows=71745 width=25) (actual time=17.720..17.721 rows=72031 loops=3)
Buckets: 65536 Batches: 2 Memory Usage: 2568kB
-> Seq Scan on table4 (cost=0.00..1244.45 rows=71745 width=25) (actual time=0.019..6.313 rows=72031 loops=3)
-> Hash (cost=893.96..893.96 rows=46996 width=40) (actual time=12.367..12.368 rows=47030 loops=3)
Buckets: 65536 Batches: 1 Memory Usage: 3865kB
-> Seq Scan on table3 (cost=0.00..893.96 rows=46996 width=40) (actual time=0.017..4.731 rows=47030 loops=3)
Planning Time: 2.249 ms
JIT:
Functions: 90
Options: Inlining true, Optimization true, Expressions true, Deforming true
Timing: Generation 8.216 ms, Inlining 84.266 ms, Optimization 596.840 ms, Emission 394.586 ms, Total 1083.909 ms
Execution Time: 32199.853 ms

Output of EXPLAIN ANALYZE:

Limit  (cost=18341692.59..18341693.89 rows=20 width=159) (actual time=32149.681..32192.245 rows=20 loops=1)
  ->  GroupAggregate  (cost=18341692.59..18353658.57 rows=184092 width=159) (actual time=31749.816..31792.379 rows=20 loops=1)
        Group Key: table2.key3, table1.key2, table4.key2, table3.key2, table2.key1, table1.key1, table1.key3, table1.key4
        ->  Sort  (cost=18341692.59..18342152.82 rows=184092 width=115) (actual time=31749.763..31792.253 rows=80 loops=1)
              Sort Key: table2.key3 DESC, table1.key2, table4.key2 DESC, table3.key2 DESC, table2.key1, table1.key1, table1.key3, table1.key4
              Sort Method: external merge  Disk: 23424kB
              ->  Gather  (cost=83595.20..18314267.69 rows=184092 width=115) (actual time=31417.800..31681.660 rows=186540 loops=1)
                    Workers Planned: 2
                    Workers Launched: 2
                    ->  Hash Left Join  (cost=82595.20..18294858.49 rows=76705 width=115) (actual time=31392.569..31545.627 rows=62180 loops=3)
                          Hash Cond: ((table1.key4)::text = (table3.key1)::text)
                          ->  Hash Left Join  (cost=81113.79..18293175.70 rows=76705 width=90) (actual time=31379.926..31524.528 rows=62180 loops=3)
                                Hash Cond: ((table1.key3)::text = (table4.key1)::text)
                                ->  Parallel Hash Left Join  (cost=78481.53..18288053.08 rows=76705 width=72) (actual time=31361.889..31481.346 rows=62180 loops=3)
                                      Hash Cond: (table1.key1 = table2.key2)
                                      ->  Parallel Seq Scan on table1  (cost=0.00..18200395.20 rows=76705 width=60) (actual time=2.888..30824.027 rows=62180 loops=3)
                                            Filter: ((key10 >= '2023-10-31 23:00:00+00'::timestamp with time zone) AND (key10 "<= '2023-11-02 23:00:00+00'::timestamp with time zone))
                                            Rows Removed by Filter: 43882417
                                      ->  Parallel Hash  (cost=52397.57..52397.57 rows=1500557 width=16) (actual time=497.607..497.608 rows=1200445 loops=3)
                                            Buckets: 131072  Batches: 64  Memory Usage: 3712kB
                                            ->  Parallel Seq Scan on table2  (cost=0.00..52397.57 rows=1500557 width=16) (actual time=225.666..349.851 rows=1200445 loops=3)
                                ->  Hash  (cost=1244.45..1244.45 rows=71745 width=25) (actual time=17.720..17.721 rows=72031 loops=3)
                                      Buckets: 65536  Batches: 2  Memory Usage: 2568kB
                                      ->  Seq Scan on table4  (cost=0.00..1244.45 rows=71745 width=25) (actual time=0.019..6.313 rows=72031 loops=3)
                          ->  Hash  (cost=893.96..893.96 rows=46996 width=40) (actual time=12.367..12.368 rows=47030 loops=3)
                                Buckets: 65536  Batches: 1  Memory Usage: 3865kB
                                ->  Seq Scan on table3  (cost=0.00..893.96 rows=46996 width=40) (actual time=0.017..4.731 rows=47030 loops=3)
Planning Time: 2.249 ms
JIT:
  Functions: 90
  Options: Inlining true, Optimization true, Expressions true, Deforming true
  Timing: Generation 8.216 ms, Inlining 84.266 ms, Optimization 596.840 ms, Emission 394.586 ms, Total 1083.909 ms
Execution Time: 32199.853 ms
restored lost > character
Source Link
Zegarek
Zegarek
  • 29.9k
  • 5
  • 27
  • 32
QUERY PLAN
Limit (cost=18341692.59..18341693.89 rows=20 width=159) (actual time=32149.681..32192.245 rows=20 loops=1)
-> GroupAggregate (cost=18341692.59..18353658.57 rows=184092 width=159) (actual time=31749.816..31792.379 rows=20 loops=1)
Group Key: table2.key3, table1.key2, table4.key2, table3.key2, table2.key1, table1.key1, table1.key3, table1.key4
-> Sort (cost=18341692.59..18342152.82 rows=184092 width=115) (actual time=31749.763..31792.253 rows=80 loops=1)
Sort Key: table2.key3 DESC, table1.key2, table4.key2 DESC, table3.key2 DESC, table2.key1, table1.key1, table1.key3, table1.key4
Sort Method: external merge Disk: 23424kB
-> Gather (cost=83595.20..18314267.69 rows=184092 width=115) (actual time=31417.800..31681.660 rows=186540 loops=1)
Workers Planned: 2
Workers Launched: 2
-> Hash Left Join (cost=82595.20..18294858.49 rows=76705 width=115) (actual time=31392.569..31545.627 rows=62180 loops=3)
Hash Cond: ((table1.key4)::text = (table3.key1)::text)
-> Hash Left Join (cost=81113.79..18293175.70 rows=76705 width=90) (actual time=31379.926..31524.528 rows=62180 loops=3)
Hash Cond: ((table1.key3)::text = (table4.key1)::text)
-> Parallel Hash Left Join (cost=78481.53..18288053.08 rows=76705 width=72) (actual time=31361.889..31481.346 rows=62180 loops=3)
Hash Cond: (table1.key1 = table2.key2)
-> Parallel Seq Scan on table1 (cost=0.00..18200395.20 rows=76705 width=60) (actual time=2.888..30824.027 rows=62180 loops=3)
Filter: ((key10 =>= '2023-10-31 23:00:00+00'::timestamp with time zone) AND (key10 "<= '2023-11-02 23:00:00+00'::timestamp with time zone))
Rows Removed by Filter: 43882417
-> Parallel Hash (cost=52397.57..52397.57 rows=1500557 width=16) (actual time=497.607..497.608 rows=1200445 loops=3)
Buckets: 131072 Batches: 64 Memory Usage: 3712kB
-> Parallel Seq Scan on table2 (cost=0.00..52397.57 rows=1500557 width=16) (actual time=225.666..349.851 rows=1200445 loops=3)
-> Hash (cost=1244.45..1244.45 rows=71745 width=25) (actual time=17.720..17.721 rows=72031 loops=3)
Buckets: 65536 Batches: 2 Memory Usage: 2568kB
-> Seq Scan on table4 (cost=0.00..1244.45 rows=71745 width=25) (actual time=0.019..6.313 rows=72031 loops=3)
-> Hash (cost=893.96..893.96 rows=46996 width=40) (actual time=12.367..12.368 rows=47030 loops=3)
Buckets: 65536 Batches: 1 Memory Usage: 3865kB
-> Seq Scan on table3 (cost=0.00..893.96 rows=46996 width=40) (actual time=0.017..4.731 rows=47030 loops=3)
Planning Time: 2.249 ms
JIT:
Functions: 90
Options: Inlining true, Optimization true, Expressions true, Deforming true
Timing: Generation 8.216 ms, Inlining 84.266 ms, Optimization 596.840 ms, Emission 394.586 ms, Total 1083.909 ms
Execution Time: 32199.853 ms
QUERY PLAN
Limit (cost=18341692.59..18341693.89 rows=20 width=159) (actual time=32149.681..32192.245 rows=20 loops=1)
-> GroupAggregate (cost=18341692.59..18353658.57 rows=184092 width=159) (actual time=31749.816..31792.379 rows=20 loops=1)
Group Key: table2.key3, table1.key2, table4.key2, table3.key2, table2.key1, table1.key1, table1.key3, table1.key4
-> Sort (cost=18341692.59..18342152.82 rows=184092 width=115) (actual time=31749.763..31792.253 rows=80 loops=1)
Sort Key: table2.key3 DESC, table1.key2, table4.key2 DESC, table3.key2 DESC, table2.key1, table1.key1, table1.key3, table1.key4
Sort Method: external merge Disk: 23424kB
-> Gather (cost=83595.20..18314267.69 rows=184092 width=115) (actual time=31417.800..31681.660 rows=186540 loops=1)
Workers Planned: 2
Workers Launched: 2
-> Hash Left Join (cost=82595.20..18294858.49 rows=76705 width=115) (actual time=31392.569..31545.627 rows=62180 loops=3)
Hash Cond: ((table1.key4)::text = (table3.key1)::text)
-> Hash Left Join (cost=81113.79..18293175.70 rows=76705 width=90) (actual time=31379.926..31524.528 rows=62180 loops=3)
Hash Cond: ((table1.key3)::text = (table4.key1)::text)
-> Parallel Hash Left Join (cost=78481.53..18288053.08 rows=76705 width=72) (actual time=31361.889..31481.346 rows=62180 loops=3)
Hash Cond: (table1.key1 = table2.key2)
-> Parallel Seq Scan on table1 (cost=0.00..18200395.20 rows=76705 width=60) (actual time=2.888..30824.027 rows=62180 loops=3)
Filter: ((key10 = '2023-10-31 23:00:00+00'::timestamp with time zone) AND (key10 "<= '2023-11-02 23:00:00+00'::timestamp with time zone))
Rows Removed by Filter: 43882417
-> Parallel Hash (cost=52397.57..52397.57 rows=1500557 width=16) (actual time=497.607..497.608 rows=1200445 loops=3)
Buckets: 131072 Batches: 64 Memory Usage: 3712kB
-> Parallel Seq Scan on table2 (cost=0.00..52397.57 rows=1500557 width=16) (actual time=225.666..349.851 rows=1200445 loops=3)
-> Hash (cost=1244.45..1244.45 rows=71745 width=25) (actual time=17.720..17.721 rows=72031 loops=3)
Buckets: 65536 Batches: 2 Memory Usage: 2568kB
-> Seq Scan on table4 (cost=0.00..1244.45 rows=71745 width=25) (actual time=0.019..6.313 rows=72031 loops=3)
-> Hash (cost=893.96..893.96 rows=46996 width=40) (actual time=12.367..12.368 rows=47030 loops=3)
Buckets: 65536 Batches: 1 Memory Usage: 3865kB
-> Seq Scan on table3 (cost=0.00..893.96 rows=46996 width=40) (actual time=0.017..4.731 rows=47030 loops=3)
Planning Time: 2.249 ms
JIT:
Functions: 90
Options: Inlining true, Optimization true, Expressions true, Deforming true
Timing: Generation 8.216 ms, Inlining 84.266 ms, Optimization 596.840 ms, Emission 394.586 ms, Total 1083.909 ms
Execution Time: 32199.853 ms
QUERY PLAN
Limit (cost=18341692.59..18341693.89 rows=20 width=159) (actual time=32149.681..32192.245 rows=20 loops=1)
-> GroupAggregate (cost=18341692.59..18353658.57 rows=184092 width=159) (actual time=31749.816..31792.379 rows=20 loops=1)
Group Key: table2.key3, table1.key2, table4.key2, table3.key2, table2.key1, table1.key1, table1.key3, table1.key4
-> Sort (cost=18341692.59..18342152.82 rows=184092 width=115) (actual time=31749.763..31792.253 rows=80 loops=1)
Sort Key: table2.key3 DESC, table1.key2, table4.key2 DESC, table3.key2 DESC, table2.key1, table1.key1, table1.key3, table1.key4
Sort Method: external merge Disk: 23424kB
-> Gather (cost=83595.20..18314267.69 rows=184092 width=115) (actual time=31417.800..31681.660 rows=186540 loops=1)
Workers Planned: 2
Workers Launched: 2
-> Hash Left Join (cost=82595.20..18294858.49 rows=76705 width=115) (actual time=31392.569..31545.627 rows=62180 loops=3)
Hash Cond: ((table1.key4)::text = (table3.key1)::text)
-> Hash Left Join (cost=81113.79..18293175.70 rows=76705 width=90) (actual time=31379.926..31524.528 rows=62180 loops=3)
Hash Cond: ((table1.key3)::text = (table4.key1)::text)
-> Parallel Hash Left Join (cost=78481.53..18288053.08 rows=76705 width=72) (actual time=31361.889..31481.346 rows=62180 loops=3)
Hash Cond: (table1.key1 = table2.key2)
-> Parallel Seq Scan on table1 (cost=0.00..18200395.20 rows=76705 width=60) (actual time=2.888..30824.027 rows=62180 loops=3)
Filter: ((key10 >= '2023-10-31 23:00:00+00'::timestamp with time zone) AND (key10 "<= '2023-11-02 23:00:00+00'::timestamp with time zone))
Rows Removed by Filter: 43882417
-> Parallel Hash (cost=52397.57..52397.57 rows=1500557 width=16) (actual time=497.607..497.608 rows=1200445 loops=3)
Buckets: 131072 Batches: 64 Memory Usage: 3712kB
-> Parallel Seq Scan on table2 (cost=0.00..52397.57 rows=1500557 width=16) (actual time=225.666..349.851 rows=1200445 loops=3)
-> Hash (cost=1244.45..1244.45 rows=71745 width=25) (actual time=17.720..17.721 rows=72031 loops=3)
Buckets: 65536 Batches: 2 Memory Usage: 2568kB
-> Seq Scan on table4 (cost=0.00..1244.45 rows=71745 width=25) (actual time=0.019..6.313 rows=72031 loops=3)
-> Hash (cost=893.96..893.96 rows=46996 width=40) (actual time=12.367..12.368 rows=47030 loops=3)
Buckets: 65536 Batches: 1 Memory Usage: 3865kB
-> Seq Scan on table3 (cost=0.00..893.96 rows=46996 width=40) (actual time=0.017..4.731 rows=47030 loops=3)
Planning Time: 2.249 ms
JIT:
Functions: 90
Options: Inlining true, Optimization true, Expressions true, Deforming true
Timing: Generation 8.216 ms, Inlining 84.266 ms, Optimization 596.840 ms, Emission 394.586 ms, Total 1083.909 ms
Execution Time: 32199.853 ms
Formatting, naming cleanup, title
Source Link
Zegarek
Zegarek
  • 29.9k
  • 5
  • 27
  • 32
remove <key11>
Source Link
Chamath
Chamath
  • 2k
  • 3
  • 23
  • 31
document table == <table1>
Source Link
Chamath
Chamath
  • 2k
  • 3
  • 23
  • 31
Notice added Canonical answer required by Chamath
Bounty Started worth 100 reputation by Chamath
key10
Source Link
Chamath
Chamath
  • 2k
  • 3
  • 23
  • 31
missing ddf
Source Link
Chamath
Chamath
  • 2k
  • 3
  • 23
  • 31
Updated based on @Frank comment
Source Link
Chamath
Chamath
  • 2k
  • 3
  • 23
  • 31
Postgres version and query explain added
Source Link
Chamath
Chamath
  • 2k
  • 3
  • 23
  • 31
Source Link
Chamath
Chamath
  • 2k
  • 3
  • 23
  • 31