· 8 years ago · Aug 02, 2018, 12:36 PM
1CREATE SCHEMA IF NOT EXISTS lkaminski;
2
3CREATE TABLE lkaminski.table_1 (id serial, flag bool, fkey int4);
4
5INSERT INTO lkaminski.table_1 (flag, fkey)
6SELECT random() > 0.5, (random()*10)::int2+1
7 FROM generate_series(1,250);
8
9SELECT COUNT(DISTINCT CONCAT(flag, ':', fkey))
10 FROM lkaminski.table_1;
11
12CREATE OR REPLACE FUNCTION lkaminski.do_stuff(flag bool, fkey int4, OUT computed_value int4)
13LANGUAGE plpgsql
14STABLE
15AS $body$
16BEGIN
17PERFORM pg_sleep(0.01);
18SELECT COUNT(0)
19 INTO computed_value
20 /* Simplified, takes data from table, not immutable */
21 ;
22END;
23$body$;
24
25EXPLAIN (ANALYZE, VERBOSE)
26SELECT *, cnt
27 FROM lkaminski.table_1
28 CROSS JOIN lkaminski.do_stuff(flag, fkey) AS cnt;
29
30Nested Loop (cost=0.25..76.25 rows=2200 width=17) (actual time=10.265..5084.277 rows=500 loops=1)
31 Output: table_1.id, table_1.flag, table_1.fkey, cnt.computed_value, cnt.computed_value
32 -> Seq Scan on lkaminski.table_1 (cost=0.00..32.00 rows=2200 width=9) (actual time=0.047..1.027 rows=500 loops=1)
33 Output: table_1.id, table_1.flag, table_1.fkey
34 -> Function Scan on lkaminski.do_stuff cnt (cost=0.25..0.26 rows=1 width=4) (actual time=10.155..10.158 rows=1 loops=500)
35 Output: cnt.computed_value
36 Function Call: lkaminski.do_stuff(table_1.flag, table_1.fkey)
37Planning time: 0.973 ms
38Execution time: 5085.000 ms
39
40EXPLAIN (ANALYZE, VERBOSE)
41SELECT t1.*, cnt
42 FROM lkaminski.table_1 AS t1
43 CROSS JOIN LATERAL(SELECT fkey, lkaminski.do_stuff(s1.flag, s1.fkey) AS cnt
44 FROM (SELECT DISTINCT t1.flag, t1.fkey) AS s1
45 ) AS a;
46
47Nested Loop (cost=0.00..648.00 rows=2200 width=13) (actual time=10.230..5072.033 rows=500 loops=1)
48 Output: t1.id, t1.flag, t1.fkey, lkaminski.do_stuff((t1.flag), (t1.fkey))
49 -> Seq Scan on lkaminski.table_1 t1 (cost=0.00..32.00 rows=2200 width=9) (actual time=0.011..0.797 rows=500 loops=1)
50 Output: t1.id, t1.flag, t1.fkey
51 -> Unique (cost=0.00..0.01 rows=1 width=5) (actual time=0.004..0.010 rows=1 loops=500)
52 Output: (t1.flag), (t1.fkey)
53 -> Result (cost=0.00..0.01 rows=1 width=5) (actual time=0.001..0.003 rows=1 loops=500)
54 Output: t1.flag, t1.fkey
55Planning time: 0.184 ms
56Execution time: 5072.785 ms
57
58EXPLAIN (ANALYZE, VERBOSE)
59WITH t1 AS (
60 SELECT id, flag, fkey FROM lkaminski.table_1 --WHERE expensive conditions here
61), c AS (
62 SELECT fkey, lkaminski.do_stuff(flag, fkey) AS cnt
63 FROM (SELECT DISTINCT flag, fkey
64 FROM t1
65 ) AS a
66)
67SELECT id, flag, fkey, cnt
68 FROM t1
69 JOIN c USING(fkey);
70
71Hash Join (cost=208.00..411.50 rows=4400 width=13) (actual time=225.632..228.265 rows=1000 loops=1)
72 Output: t1.id, t1.flag, t1.fkey, c.cnt
73 Hash Cond: (t1.fkey = c.fkey)
74 CTE t1
75 -> Seq Scan on lkaminski.table_1 (cost=0.00..32.00 rows=2200 width=9) (actual time=0.013..0.666 rows=500 loops=1)
76 Output: table_1.id, table_1.flag, table_1.fkey
77 CTE c
78 -> Subquery Scan on a (cost=55.00..163.00 rows=400 width=8) (actual time=12.947..225.478 rows=22 loops=1)
79 Output: a.fkey, lkaminski.do_stuff(a.flag, a.fkey)
80 -> HashAggregate (cost=55.00..59.00 rows=400 width=5) (actual time=2.724..2.756 rows=22 loops=1)
81 Output: t1_1.flag, t1_1.fkey
82 Group Key: t1_1.flag, t1_1.fkey
83 -> CTE Scan on t1 t1_1 (cost=0.00..44.00 rows=2200 width=5) (actual time=0.002..2.025 rows=500 loops=1)
84 Output: t1_1.id, t1_1.flag, t1_1.fkey
85 -> CTE Scan on t1 (cost=0.00..44.00 rows=2200 width=9) (actual time=0.018..0.686 rows=500 loops=1)
86 Output: t1.id, t1.flag, t1.fkey
87 -> Hash (cost=8.00..8.00 rows=400 width=8) (actual time=225.600..225.600 rows=22 loops=1)
88 Output: c.cnt, c.fkey
89 Buckets: 1024 Batches: 1 Memory Usage: 9kB
90 -> CTE Scan on c (cost=0.00..8.00 rows=400 width=8) (actual time=12.951..225.551 rows=22 loops=1)
91 Output: c.cnt, c.fkey
92Planning time: 0.291 ms
93Execution time: 230.920 ms