By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.
select version();
| version |
|---|
| PostgreSQL 14.1 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-4), 64-bit |
CREATE TABLE cas -- French for "case" - case is an SQL keyword and should
( -- not be used for table names!
case_id INTEGER NOT NULL,
cat TEXT NOT NULL,
birth DATE NOT NULL, -- store as a date - only requires 4 bytes
c_type TEXT NULL -- type is a keyword - https://www.postgresql.org/docs/8.1/sql-keywords-appendix.html
);
INSERT INTO cas (case_id, cat, birth) VALUES
(20033738, 'CASE_OWNER', '1996-04-08'),
(20033738, 'WIFE' , '1995-08-22'),
(20033831, 'CASE_OWNER', '1975-03-05'),
(20033831, 'CHILD' , '2005-03-19'),
(20033831, 'CHILD' , '2006-03-25'),
(20033831, 'CHILD' , '2010-05-20'),
(20033831, 'CHILD' , '2013-10-25'),
(20039301, 'CASE_OWNER', '1999-07-27'),
(20039301, 'WIFE' , '2001-07-05'),
(20039301, 'CHILD' , '2018-10-22'),
(20039334, 'CASE_OWNER', '1994-03-10'),
(30033333, 'CASE_OWNER', '1980-01-01'), -- added a single case owner with one child!
(30033333, 'CHILD' , '2012-09-01'),
(30044444, 'CASE_OWNER', '2015-08-10'), -- added a case owner < 21 yrs of age!
(30055555, 'CASE_OWNER', '1970-02-10'), -- added a couple whose children are all > 21!
(30055555, 'WIFE' , '1972-07-05'),
(30055555, 'CHILD' , '1995-11-22'),
(30055555, 'CHILD' , '1997-05-19'),
(30066666, 'CASE_OWNER', '1970-02-10'), -- added single case owner whose children
(30066666, 'CHILD' , '1989-07-05'), -- are all over 21!
(30066666, 'CHILD' , '1992-11-22'),
(30066666, 'CHILD' , '1994-05-19');
22 rows affected
--
-- Query 1
--
-- Using window functions
--
--
SELECT
case_id,
cat,
birth,
c_type,
CASE
-- 1st section:
--
-- The logic here is that if the COUNT of case_id is = 1, then there can **only**
-- be a single case_owner and nobdody else is associated with the case!
--
-- Then, there's a choice between the age of the case_owner being greater than
-- or equal to 21 ('SINGLE_PERSON') or less than 21 ('UNKNOWN')
--
WHEN (COUNT(case_id) OVER (PARTITION BY case_id)) = 1
THEN
CASE
WHEN EXTRACT('YEAR' FROM AGE(birth)) >= 21
THEN 'SINGLE_PERSON'
ELSE 'UNKNOWN'
END
-- 2nd section:
| case_id | cat | birth | c_type | case |
|---|---|---|---|---|
| 20033738 | CASE_OWNER | 1996-04-08 | null | PAIR_NO_CHILD |
| 20033738 | WIFE | 1995-08-22 | null | PAIR_NO_CHILD |
| 20033831 | CASE_OWNER | 1975-03-05 | null | SINGLE_WITH_CHILD |
| 20033831 | CHILD | 2013-10-25 | null | SINGLE_WITH_CHILD |
| 20033831 | CHILD | 2010-05-20 | null | SINGLE_WITH_CHILD |
| 20033831 | CHILD | 2006-03-25 | null | SINGLE_WITH_CHILD |
| 20033831 | CHILD | 2005-03-19 | null | SINGLE_WITH_CHILD |
| 20039301 | CASE_OWNER | 1999-07-27 | null | PAIR_WITH_CHILD |
| 20039301 | WIFE | 2001-07-05 | null | PAIR_WITH_CHILD |
| 20039301 | CHILD | 2018-10-22 | null | PAIR_WITH_CHILD |
| 20039334 | CASE_OWNER | 1994-03-10 | null | SINGLE_PERSON |
| 30033333 | CASE_OWNER | 1980-01-01 | null | SINGLE_WITH_CHILD |
| 30033333 | CHILD | 2012-09-01 | null | SINGLE_WITH_CHILD |
| 30044444 | CASE_OWNER | 2015-08-10 | null | UNKNOWN |
| 30055555 | CASE_OWNER | 1970-02-10 | null | OTHER |
| 30055555 | WIFE | 1972-07-05 | null | OTHER |
| 30055555 | CHILD | 1997-05-19 | null | OTHER |
| 30055555 | CHILD | 1995-11-22 | null | OTHER |
| 30066666 | CASE_OWNER | 1970-02-10 | null | MULTIPLE |
| 30066666 | CHILD | 1994-05-19 | null | MULTIPLE |
| 30066666 | CHILD | 1992-11-22 | null | MULTIPLE |
| 30066666 | CHILD | 1989-07-05 | null | MULTIPLE |
--
-- Query 2
--
-- Using a subquery
--
--
SELECT
s.case_id,
s.cat,
s.birth,
CASE
-- 1st section:
WHEN s.c_cnt = 1
THEN
CASE
WHEN s.a_min >= 21
THEN 'SINGLE PERSON'
ELSE 'UNKNOWN'
END
-- 2nd section:
WHEN s.c_cnt = 2 AND s.w_cnt = 1
THEN 'PAIR_NO_CHILD'
-- 3rd section:
WHEN s.c_cnt >= 3 AND s.w_cnt = 1
THEN
| case_id | cat | birth | case |
|---|---|---|---|
| 20033738 | CASE_OWNER | 1996-04-08 | PAIR_NO_CHILD |
| 20033738 | WIFE | 1995-08-22 | PAIR_NO_CHILD |
| 20033831 | CASE_OWNER | 1975-03-05 | SINGLE_WITH_CHILD |
| 20033831 | CHILD | 2013-10-25 | SINGLE_WITH_CHILD |
| 20033831 | CHILD | 2010-05-20 | SINGLE_WITH_CHILD |
| 20033831 | CHILD | 2006-03-25 | SINGLE_WITH_CHILD |
| 20033831 | CHILD | 2005-03-19 | SINGLE_WITH_CHILD |
| 20039301 | CASE_OWNER | 1999-07-27 | PAIR_WITH_CHILD |
| 20039301 | WIFE | 2001-07-05 | PAIR_WITH_CHILD |
| 20039301 | CHILD | 2018-10-22 | PAIR_WITH_CHILD |
| 20039334 | CASE_OWNER | 1994-03-10 | SINGLE PERSON |
| 30033333 | CASE_OWNER | 1980-01-01 | SINGLE_WITH_CHILD |
| 30033333 | CHILD | 2012-09-01 | SINGLE_WITH_CHILD |
| 30044444 | CASE_OWNER | 2015-08-10 | UNKNOWN |
| 30055555 | CASE_OWNER | 1970-02-10 | OTHER |
| 30055555 | WIFE | 1972-07-05 | OTHER |
| 30055555 | CHILD | 1997-05-19 | OTHER |
| 30055555 | CHILD | 1995-11-22 | OTHER |
| 30066666 | CASE_OWNER | 1970-02-10 | MULTIPLE |
| 30066666 | CHILD | 1994-05-19 | MULTIPLE |
| 30066666 | CHILD | 1992-11-22 | MULTIPLE |
| 30066666 | CHILD | 1989-07-05 | MULTIPLE |
--
--
-- Performance analysis - to give every chance to the subquery approach, I'm
-- doing the perf analysis using the CTEs first - so that any caching &c. will
-- be done for the subsequenct subquery EXPLAIN (ANALYZE...)
--
--
--
-- Query 1 peformance analysis with ORDER BY - window function query
--
--
EXPLAIN (ANALYZE, BUFFERS, VERBOSE)
SELECT
s.case_id,
s.cat,
s.birth,
CASE
-- 1st section:
WHEN (COUNT(case_id) OVER (PARTITION BY case_id)) = 1
THEN
CASE
WHEN EXTRACT('YEAR' FROM AGE(birth)) >= 21
THEN 'SINGLE_PERSON'
ELSE 'UNKNOWN'
END
-- 2nd section:
WHEN COUNT(case_id) OVER (PARTITION BY case_id) = 2
AND COUNT(case_id) FILTER (WHERE cat = 'WIFE') OVER (PARTITION BY case_id) = 1
THEN 'PAIR_NO_CHILD'
-- 3rd section:
| QUERY PLAN |
|---|
| WindowAgg (cost=63.31..132.16 rows=810 width=72) (actual time=0.050..0.096 rows=22 loops=1) |
| Output: s.case_id, s.cat, s.birth, CASE WHEN (count(s.case_id) OVER (?) = 1) THEN CASE WHEN (EXTRACT(YEAR FROM age((CURRENT_DATE)::timestamp with time zone, (s.birth)::timestamp with time zone)) >= '21'::numeric) THEN 'SINGLE_PERSON'::text ELSE 'UNKNOWN'::text END WHEN ((count(s.case_id) OVER (?) = 2) AND (count(s.case_id) FILTER (WHERE (s.cat = 'WIFE'::text)) OVER (?) = 1)) THEN 'PAIR_NO_CHILD'::text WHEN ((count(s.case_id) OVER (?) >= 3) AND (count(s.case_id) FILTER (WHERE (s.cat = 'WIFE'::text)) OVER (?) = 1)) THEN CASE WHEN (min(EXTRACT(YEAR FROM age((CURRENT_DATE)::timestamp with time zone, (s.birth)::timestamp with time zone))) OVER (?) < '21'::numeric) THEN 'PAIR_WITH_CHILD'::text ELSE 'OTHER'::text END WHEN ((count(s.case_id) OVER (?) >= 2) AND (count(s.case_id) FILTER (WHERE (s.cat = 'WIFE'::text)) OVER (?) = 0)) THEN CASE WHEN (min(EXTRACT(YEAR FROM age((CURRENT_DATE)::timestamp with time zone, (s.birth)::timestamp with time zone))) OVER (?) < '21'::numeric) THEN 'SINGLE_WITH_CHILD'::text ELSE 'MULTIPLE'::text END ELSE 'No assigned type!'::text END |
| Buffers: shared hit=1 |
| -> Subquery Scan on s (cost=63.31..73.43 rows=810 width=40) (actual time=0.031..0.038 rows=22 loops=1) |
| Output: s.case_id, s.cat, s.birth |
| Buffers: shared hit=1 |
| -> Sort (cost=63.31..65.33 rows=810 width=92) (actual time=0.030..0.033 rows=22 loops=1) |
| Output: c1.case_id, c1.cat, c1.birth, NULL::bigint, NULL::bigint, NULL::numeric, (CASE WHEN (c1.cat = 'CASE_OWNER'::text) THEN 1 WHEN (c1.cat = 'WIFE'::text) THEN 2 WHEN (c1.cat = 'CHILD'::text) THEN 3 ELSE NULL::integer END) |
| Sort Key: c1.case_id, (CASE WHEN (c1.cat = 'CASE_OWNER'::text) THEN 1 WHEN (c1.cat = 'WIFE'::text) THEN 2 WHEN (c1.cat = 'CHILD'::text) THEN 3 ELSE NULL::integer END), c1.birth DESC |
| Sort Method: quicksort Memory: 26kB |
| Buffers: shared hit=1 |
| -> Seq Scan on public.cas c1 (cost=0.00..24.18 rows=810 width=92) (actual time=0.009..0.014 rows=22 loops=1) |
| Output: c1.case_id, c1.cat, c1.birth, NULL::bigint, NULL::bigint, NULL::numeric, CASE WHEN (c1.cat = 'CASE_OWNER'::text) THEN 1 WHEN (c1.cat = 'WIFE'::text) THEN 2 WHEN (c1.cat = 'CHILD'::text) THEN 3 ELSE NULL::integer END |
| Buffers: shared hit=1 |
| Planning Time: 0.143 ms |
| Execution Time: 0.184 ms |
--
--
-- Query 2 peformance analysis with ORDER BY - subquery
--
--
EXPLAIN (ANALYZE, BUFFERS, VERBOSE)
SELECT
s.case_id,
s.cat,
s.birth,
CASE
-- 1st section:
--
-- The logic here is that if the COUNT of case_id is = 1, then there can **only**
-- be a single case_owner and nobdody else is associated with the case!
--
-- Then, there's a choice between the age of the case_owner being greater than
-- or equal to 21 ('SINGLE_PERSON') or less than 21 ('UNKNOWN')
--
WHEN s.c_cnt = 1
THEN
CASE
WHEN s.a_min >= 21
THEN 'SINGLE PERSON'
ELSE 'UNKNOWN'
END
-- 2nd section:
--
-- The logic is that if the COUNT of case_id is = 2 **and** there's a 'WIFE', then
| QUERY PLAN |
|---|
| Subquery Scan on s (cost=49161.46..49191.83 rows=810 width=72) (actual time=0.685..0.696 rows=22 loops=1) |
| Output: s.case_id, s.cat, s.birth, CASE WHEN (s.c_cnt = 1) THEN CASE WHEN (s.a_min >= '21'::numeric) THEN 'SINGLE PERSON'::text ELSE 'UNKNOWN'::text END WHEN ((s.c_cnt = 2) AND (s.w_cnt = 1)) THEN 'PAIR_NO_CHILD'::text WHEN ((s.c_cnt >= 3) AND (s.w_cnt = 1)) THEN CASE WHEN (s.a_min < '21'::numeric) THEN 'PAIR_WITH_CHILD'::text ELSE 'OTHER'::text END WHEN ((s.c_cnt >= 2) AND (s.w_cnt = 0)) THEN CASE WHEN (s.a_min < '21'::numeric) THEN 'SINGLE_WITH_CHILD'::text ELSE 'MULTIPLE'::text END ELSE 'No assigned type!'::text END |
| Buffers: shared hit=67 |
| -> Sort (cost=49161.46..49163.48 rows=810 width=92) (actual time=0.683..0.686 rows=22 loops=1) |
| Output: c1.case_id, c1.cat, c1.birth, ((SubPlan 1)), ((SubPlan 2)), ((SubPlan 3)), (CASE WHEN (c1.cat = 'CASE_OWNER'::text) THEN 1 WHEN (c1.cat = 'WIFE'::text) THEN 2 WHEN (c1.cat = 'CHILD'::text) THEN 3 ELSE NULL::integer END) |
| Sort Key: c1.case_id, (CASE WHEN (c1.cat = 'CASE_OWNER'::text) THEN 1 WHEN (c1.cat = 'WIFE'::text) THEN 2 WHEN (c1.cat = 'CHILD'::text) THEN 3 ELSE NULL::integer END), c1.birth DESC |
| Sort Method: quicksort Memory: 27kB |
| Buffers: shared hit=67 |
| -> Seq Scan on public.cas c1 (cost=0.00..49122.32 rows=810 width=92) (actual time=0.051..0.661 rows=22 loops=1) |
| Output: c1.case_id, c1.cat, c1.birth, (SubPlan 1), (SubPlan 2), (SubPlan 3), CASE WHEN (c1.cat = 'CASE_OWNER'::text) THEN 1 WHEN (c1.cat = 'WIFE'::text) THEN 2 WHEN (c1.cat = 'CHILD'::text) THEN 3 ELSE NULL::integer END |
| Buffers: shared hit=67 |
| SubPlan 1 |
| -> GroupAggregate (cost=0.00..20.19 rows=4 width=12) (actual time=0.007..0.007 rows=1 loops=22) |
| Output: count(c2.case_id), c2.case_id |
| Group Key: c2.case_id |
| Buffers: shared hit=22 |
| -> Seq Scan on public.cas c2 (cost=0.00..20.12 rows=4 width=4) (actual time=0.003..0.004 rows=3 loops=22) |
| Output: c2.case_id, c2.cat, c2.birth, c2.c_type |
| Filter: (c2.case_id = c1.case_id) |
| Rows Removed by Filter: 19 |
| Buffers: shared hit=22 |
| SubPlan 2 |
| -> GroupAggregate (cost=0.00..20.20 rows=4 width=12) (actual time=0.007..0.007 rows=1 loops=22) |
| Output: sum(CASE WHEN (c3.cat = 'WIFE'::text) THEN 1 ELSE 0 END), c3.case_id |
| Group Key: c3.case_id |
| Buffers: shared hit=22 |
| -> Seq Scan on public.cas c3 (cost=0.00..20.12 rows=4 width=36) (actual time=0.003..0.004 rows=3 loops=22) |
| Output: c3.case_id, c3.cat, c3.birth, c3.c_type |
| Filter: (c3.case_id = c1.case_id) |
| Rows Removed by Filter: 19 |
| Buffers: shared hit=22 |
| SubPlan 3 |
| -> GroupAggregate (cost=0.00..20.23 rows=4 width=36) (actual time=0.012..0.012 rows=1 loops=22) |
| Output: min(EXTRACT(YEAR FROM age((CURRENT_DATE)::timestamp with time zone, (c4.birth)::timestamp with time zone))), c4.case_id |
| Group Key: c4.case_id |
| Buffers: shared hit=22 |
| -> Seq Scan on public.cas c4 (cost=0.00..20.12 rows=4 width=8) (actual time=0.002..0.003 rows=3 loops=22) |
| Output: c4.case_id, c4.cat, c4.birth, c4.c_type |
| Filter: (c4.case_id = c1.case_id) |
| Rows Removed by Filter: 19 |
| Buffers: shared hit=22 |
| Planning Time: 0.190 ms |
| Execution Time: 0.750 ms |
--
--
-- Performance analysis WITHOUT ORDER BY at end
--
--
--
--
-- Query 1 peformance analysis without ORDER BY - window function query
--
--
EXPLAIN (ANALYZE, BUFFERS, VERBOSE)
SELECT
case_id,
cat,
birth,
c_type,
CASE
-- 1st section:
--
-- The logic here is that if the COUNT of case_id is = 1, then there can **only**
-- be a single case_owner and nobdody else is associated with the case!
--
-- Then, there's a choice between the age of the case_owner being greater than
-- or equal to 21 ('SINGLE_PERSON') or less than 21 ('UNKNOWN')
--
WHEN (COUNT(case_id) OVER (PARTITION BY case_id)) = 1
THEN
CASE
WHEN EXTRACT('YEAR' FROM AGE(birth)) >= 21
THEN 'SINGLE_PERSON'
ELSE 'UNKNOWN'
END
-- 2nd section:
| QUERY PLAN |
|---|
| WindowAgg (cost=57.23..117.98 rows=810 width=104) (actual time=0.028..0.067 rows=22 loops=1) |
| Output: case_id, cat, birth, c_type, CASE WHEN (count(case_id) OVER (?) = 1) THEN CASE WHEN (EXTRACT(YEAR FROM age((CURRENT_DATE)::timestamp with time zone, (birth)::timestamp with time zone)) >= '21'::numeric) THEN 'SINGLE_PERSON'::text ELSE 'UNKNOWN'::text END WHEN ((count(case_id) OVER (?) = 2) AND (count(case_id) FILTER (WHERE (cat = 'WIFE'::text)) OVER (?) = 1)) THEN 'PAIR_NO_CHILD'::text WHEN ((count(case_id) OVER (?) >= 3) AND (count(case_id) FILTER (WHERE (cat = 'WIFE'::text)) OVER (?) = 1)) THEN CASE WHEN (min(EXTRACT(YEAR FROM age((CURRENT_DATE)::timestamp with time zone, (birth)::timestamp with time zone))) OVER (?) < '21'::numeric) THEN 'PAIR_WITH_CHILD'::text ELSE 'OTHER'::text END WHEN ((count(case_id) OVER (?) >= 2) AND (count(case_id) FILTER (WHERE (cat = 'WIFE'::text)) OVER (?) = 0)) THEN CASE WHEN (min(EXTRACT(YEAR FROM age((CURRENT_DATE)::timestamp with time zone, (birth)::timestamp with time zone))) OVER (?) < '21'::numeric) THEN 'SINGLE_WITH_CHILD'::text ELSE 'MULTIPLE'::text END ELSE 'No assigned type!'::text END |
| Buffers: shared hit=1 |
| -> Sort (cost=57.23..59.26 rows=810 width=72) (actual time=0.015..0.017 rows=22 loops=1) |
| Output: case_id, cat, birth, c_type |
| Sort Key: cas.case_id |
| Sort Method: quicksort Memory: 26kB |
| Buffers: shared hit=1 |
| -> Seq Scan on public.cas (cost=0.00..18.10 rows=810 width=72) (actual time=0.008..0.010 rows=22 loops=1) |
| Output: case_id, cat, birth, c_type |
| Buffers: shared hit=1 |
| Planning Time: 0.109 ms |
| Execution Time: 0.102 ms |
--
--
-- Performance analysis with subquery without ORDER BY at end of query
--
--
EXPLAIN (ANALYZE, BUFFERS, VERBOSE)
SELECT
s.case_id,
s.cat,
s.birth,
CASE
WHEN s.c_cnt = 1
THEN
CASE
WHEN EXTRACT('YEAR' FROM AGE(birth)) >= 21
THEN 'SINGLE PERSON'
ELSE 'UNKNOWN'
END
WHEN s.c_cnt = 2 AND s.w_cnt = 1
THEN 'PAIR_NO_CHILD'
WHEN s.c_cnt >= 3 AND s.w_cnt = 1
THEN
CASE
WHEN s.a_min < 21
THEN 'PAIR_WITH_CHILD'
ELSE 'OTHER'
END
WHEN s.c_cnt >= 2 AND s.w_cnt = 0
THEN
| QUERY PLAN |
|---|
| Seq Scan on public.cas c1 (cost=0.00..147302.42 rows=810 width=72) (actual time=0.045..1.080 rows=22 loops=1) |
| Output: c1.case_id, c1.cat, c1.birth, CASE WHEN ((SubPlan 1) = 1) THEN CASE WHEN (EXTRACT(YEAR FROM age((CURRENT_DATE)::timestamp with time zone, (c1.birth)::timestamp with time zone)) >= '21'::numeric) THEN 'SINGLE PERSON'::text ELSE 'UNKNOWN'::text END WHEN (((SubPlan 2) = 2) AND ((SubPlan 3) = 1)) THEN 'PAIR_NO_CHILD'::text WHEN (((SubPlan 4) >= 3) AND ((SubPlan 5) = 1)) THEN CASE WHEN ((SubPlan 6) < '21'::numeric) THEN 'PAIR_WITH_CHILD'::text ELSE 'OTHER'::text END WHEN (((SubPlan 7) >= 2) AND ((SubPlan 8) = 0)) THEN CASE WHEN ((SubPlan 9) < '21'::numeric) THEN 'SINGLE_WITH_CHILD'::text ELSE 'MULTIPLE'::text END ELSE 'No assigned type!'::text END |
| Buffers: shared hit=121 |
| SubPlan 1 |
| -> GroupAggregate (cost=0.00..20.19 rows=4 width=12) (actual time=0.007..0.007 rows=1 loops=22) |
| Output: count(c2.case_id), c2.case_id |
| Group Key: c2.case_id |
| Buffers: shared hit=22 |
| -> Seq Scan on public.cas c2 (cost=0.00..20.12 rows=4 width=4) (actual time=0.003..0.003 rows=3 loops=22) |
| Output: c2.case_id, c2.cat, c2.birth, c2.c_type |
| Filter: (c2.case_id = c1.case_id) |
| Rows Removed by Filter: 19 |
| Buffers: shared hit=22 |
| SubPlan 2 |
| -> GroupAggregate (cost=0.00..20.19 rows=4 width=12) (actual time=0.007..0.007 rows=1 loops=20) |
| Output: count(c2_1.case_id), c2_1.case_id |
| Group Key: c2_1.case_id |
| Buffers: shared hit=20 |
| -> Seq Scan on public.cas c2_1 (cost=0.00..20.12 rows=4 width=4) (actual time=0.002..0.003 rows=4 loops=20) |
| Output: c2_1.case_id, c2_1.cat, c2_1.birth, c2_1.c_type |
| Filter: (c2_1.case_id = c1.case_id) |
| Rows Removed by Filter: 18 |
| Buffers: shared hit=20 |
| SubPlan 3 |
| -> GroupAggregate (cost=0.00..20.20 rows=4 width=12) (actual time=0.007..0.007 rows=1 loops=4) |
| Output: sum(CASE WHEN (c3.cat = 'WIFE'::text) THEN 1 ELSE 0 END), c3.case_id |
| Group Key: c3.case_id |
| Buffers: shared hit=4 |
| -> Seq Scan on public.cas c3 (cost=0.00..20.12 rows=4 width=36) (actual time=0.003..0.004 rows=2 loops=4) |
| Output: c3.case_id, c3.cat, c3.birth, c3.c_type |
| Filter: (c3.case_id = c1.case_id) |
| Rows Removed by Filter: 20 |
| Buffers: shared hit=4 |
| SubPlan 4 |
| -> GroupAggregate (cost=0.00..20.19 rows=4 width=12) (actual time=0.006..0.007 rows=1 loops=18) |
| Output: count(c2_2.case_id), c2_2.case_id |
| Group Key: c2_2.case_id |
| Buffers: shared hit=18 |
| -> Seq Scan on public.cas c2_2 (cost=0.00..20.12 rows=4 width=4) (actual time=0.002..0.003 rows=4 loops=18) |
| Output: c2_2.case_id, c2_2.cat, c2_2.birth, c2_2.c_type |
| Filter: (c2_2.case_id = c1.case_id) |
| Rows Removed by Filter: 18 |
| Buffers: shared hit=18 |
| SubPlan 5 |
| -> GroupAggregate (cost=0.00..20.20 rows=4 width=12) (actual time=0.008..0.008 rows=1 loops=16) |
| Output: sum(CASE WHEN (c3_1.cat = 'WIFE'::text) THEN 1 ELSE 0 END), c3_1.case_id |
| Group Key: c3_1.case_id |
| Buffers: shared hit=16 |
| -> Seq Scan on public.cas c3_1 (cost=0.00..20.12 rows=4 width=36) (actual time=0.003..0.004 rows=4 loops=16) |
| Output: c3_1.case_id, c3_1.cat, c3_1.birth, c3_1.c_type |
| Filter: (c3_1.case_id = c1.case_id) |
| Rows Removed by Filter: 18 |
| Buffers: shared hit=16 |
| SubPlan 6 |
| -> GroupAggregate (cost=0.00..20.23 rows=4 width=36) (actual time=0.011..0.011 rows=1 loops=7) |
| Output: min(EXTRACT(YEAR FROM age((CURRENT_DATE)::timestamp with time zone, (c4.birth)::timestamp with time zone))), c4.case_id |
| Group Key: c4.case_id |
| Buffers: shared hit=7 |
| -> Seq Scan on public.cas c4 (cost=0.00..20.12 rows=4 width=8) (actual time=0.003..0.004 rows=4 loops=7) |
| Output: c4.case_id, c4.cat, c4.birth, c4.c_type |
| Filter: (c4.case_id = c1.case_id) |
| Rows Removed by Filter: 18 |
| Buffers: shared hit=7 |
| SubPlan 7 |
| -> GroupAggregate (cost=0.00..20.19 rows=4 width=12) (actual time=0.007..0.007 rows=1 loops=11) |
| Output: count(c2_3.case_id), c2_3.case_id |
| Group Key: c2_3.case_id |
| Buffers: shared hit=11 |
| -> Seq Scan on public.cas c2_3 (cost=0.00..20.12 rows=4 width=4) (actual time=0.003..0.004 rows=4 loops=11) |
| Output: c2_3.case_id, c2_3.cat, c2_3.birth, c2_3.c_type |
| Filter: (c2_3.case_id = c1.case_id) |
| Rows Removed by Filter: 18 |
| Buffers: shared hit=11 |
| SubPlan 8 |
| -> GroupAggregate (cost=0.00..20.20 rows=4 width=12) (actual time=0.007..0.007 rows=1 loops=11) |
| Output: sum(CASE WHEN (c3_2.cat = 'WIFE'::text) THEN 1 ELSE 0 END), c3_2.case_id |
| Group Key: c3_2.case_id |
| Buffers: shared hit=11 |
| -> Seq Scan on public.cas c3_2 (cost=0.00..20.12 rows=4 width=36) (actual time=0.003..0.003 rows=4 loops=11) |
| Output: c3_2.case_id, c3_2.cat, c3_2.birth, c3_2.c_type |
| Filter: (c3_2.case_id = c1.case_id) |
| Rows Removed by Filter: 18 |
| Buffers: shared hit=11 |
| SubPlan 9 |
| -> GroupAggregate (cost=0.00..20.23 rows=4 width=36) (actual time=0.012..0.012 rows=1 loops=11) |
| Output: min(EXTRACT(YEAR FROM age((CURRENT_DATE)::timestamp with time zone, (c4_1.birth)::timestamp with time zone))), c4_1.case_id |
| Group Key: c4_1.case_id |
| Buffers: shared hit=11 |
| -> Seq Scan on public.cas c4_1 (cost=0.00..20.12 rows=4 width=8) (actual time=0.003..0.004 rows=4 loops=11) |
| Output: c4_1.case_id, c4_1.cat, c4_1.birth, c4_1.c_type |
| Filter: (c4_1.case_id = c1.case_id) |
| Rows Removed by Filter: 18 |
| Buffers: shared hit=11 |
| Planning Time: 0.444 ms |
| Execution Time: 1.340 ms |
CREATE INDEX case_id_ix ON cas (case_id);
CREATE INDEX cat_ix ON cas (cat);
CREATE INDEX birth_ix ON cas (birth);
--
--
-- Query 1 peformance analysis with ORDER BY - window function query
--
--
EXPLAIN (ANALYZE, BUFFERS, VERBOSE)
SELECT
s.case_id,
s.cat,
s.birth,
CASE
-- 1st section:
WHEN (COUNT(case_id) OVER (PARTITION BY case_id)) = 1
THEN
CASE
WHEN EXTRACT('YEAR' FROM AGE(birth)) >= 21
THEN 'SINGLE_PERSON'
ELSE 'UNKNOWN'
END
-- 2nd section:
WHEN COUNT(case_id) OVER (PARTITION BY case_id) = 2
AND COUNT(case_id) FILTER (WHERE cat = 'WIFE') OVER (PARTITION BY case_id) = 1
THEN 'PAIR_NO_CHILD'
-- 3rd section:
| QUERY PLAN |
|---|
| WindowAgg (cost=1.88..3.75 rows=22 width=72) (actual time=0.082..0.130 rows=22 loops=1) |
| Output: s.case_id, s.cat, s.birth, CASE WHEN (count(s.case_id) OVER (?) = 1) THEN CASE WHEN (EXTRACT(YEAR FROM age((CURRENT_DATE)::timestamp with time zone, (s.birth)::timestamp with time zone)) >= '21'::numeric) THEN 'SINGLE_PERSON'::text ELSE 'UNKNOWN'::text END WHEN ((count(s.case_id) OVER (?) = 2) AND (count(s.case_id) FILTER (WHERE (s.cat = 'WIFE'::text)) OVER (?) = 1)) THEN 'PAIR_NO_CHILD'::text WHEN ((count(s.case_id) OVER (?) >= 3) AND (count(s.case_id) FILTER (WHERE (s.cat = 'WIFE'::text)) OVER (?) = 1)) THEN CASE WHEN (min(EXTRACT(YEAR FROM age((CURRENT_DATE)::timestamp with time zone, (s.birth)::timestamp with time zone))) OVER (?) < '21'::numeric) THEN 'PAIR_WITH_CHILD'::text ELSE 'OTHER'::text END WHEN ((count(s.case_id) OVER (?) >= 2) AND (count(s.case_id) FILTER (WHERE (s.cat = 'WIFE'::text)) OVER (?) = 0)) THEN CASE WHEN (min(EXTRACT(YEAR FROM age((CURRENT_DATE)::timestamp with time zone, (s.birth)::timestamp with time zone))) OVER (?) < '21'::numeric) THEN 'SINGLE_WITH_CHILD'::text ELSE 'MULTIPLE'::text END ELSE 'No assigned type!'::text END |
| Buffers: shared hit=1 |
| -> Subquery Scan on s (cost=1.88..2.15 rows=22 width=40) (actual time=0.056..0.062 rows=22 loops=1) |
| Output: s.case_id, s.cat, s.birth |
| Buffers: shared hit=1 |
| -> Sort (cost=1.88..1.93 rows=22 width=92) (actual time=0.054..0.057 rows=22 loops=1) |
| Output: c1.case_id, c1.cat, c1.birth, NULL::bigint, NULL::bigint, NULL::numeric, (CASE WHEN (c1.cat = 'CASE_OWNER'::text) THEN 1 WHEN (c1.cat = 'WIFE'::text) THEN 2 WHEN (c1.cat = 'CHILD'::text) THEN 3 ELSE NULL::integer END) |
| Sort Key: c1.case_id, (CASE WHEN (c1.cat = 'CASE_OWNER'::text) THEN 1 WHEN (c1.cat = 'WIFE'::text) THEN 2 WHEN (c1.cat = 'CHILD'::text) THEN 3 ELSE NULL::integer END), c1.birth DESC |
| Sort Method: quicksort Memory: 26kB |
| Buffers: shared hit=1 |
| -> Seq Scan on public.cas c1 (cost=0.00..1.39 rows=22 width=92) (actual time=0.026..0.031 rows=22 loops=1) |
| Output: c1.case_id, c1.cat, c1.birth, NULL::bigint, NULL::bigint, NULL::numeric, CASE WHEN (c1.cat = 'CASE_OWNER'::text) THEN 1 WHEN (c1.cat = 'WIFE'::text) THEN 2 WHEN (c1.cat = 'CHILD'::text) THEN 3 ELSE NULL::integer END |
| Buffers: shared hit=1 |
| Planning: |
| Buffers: shared hit=49 read=6 dirtied=1 |
| Planning Time: 0.516 ms |
| Execution Time: 0.177 ms |
--
--
-- Performance analysis with subquery without ORDER BY at end of query
--
--
EXPLAIN (ANALYZE, BUFFERS, VERBOSE)
SELECT
s.case_id,
s.cat,
s.birth,
CASE
WHEN s.c_cnt = 1
THEN
CASE
WHEN EXTRACT('YEAR' FROM AGE(birth)) >= 21
THEN 'SINGLE PERSON'
ELSE 'UNKNOWN'
END
WHEN s.c_cnt = 2 AND s.w_cnt = 1
THEN 'PAIR_NO_CHILD'
WHEN s.c_cnt >= 3 AND s.w_cnt = 1
THEN
CASE
WHEN s.a_min < 21
THEN 'PAIR_WITH_CHILD'
ELSE 'OTHER'
END
WHEN s.c_cnt >= 2 AND s.w_cnt = 0
THEN
| QUERY PLAN |
|---|
| Seq Scan on public.cas c1 (cost=0.00..258.18 rows=22 width=72) (actual time=0.042..0.981 rows=22 loops=1) |
| Output: c1.case_id, c1.cat, c1.birth, CASE WHEN ((SubPlan 1) = 1) THEN CASE WHEN (EXTRACT(YEAR FROM age((CURRENT_DATE)::timestamp with time zone, (c1.birth)::timestamp with time zone)) >= '21'::numeric) THEN 'SINGLE PERSON'::text ELSE 'UNKNOWN'::text END WHEN (((SubPlan 2) = 2) AND ((SubPlan 3) = 1)) THEN 'PAIR_NO_CHILD'::text WHEN (((SubPlan 4) >= 3) AND ((SubPlan 5) = 1)) THEN CASE WHEN ((SubPlan 6) < '21'::numeric) THEN 'PAIR_WITH_CHILD'::text ELSE 'OTHER'::text END WHEN (((SubPlan 7) >= 2) AND ((SubPlan 8) = 0)) THEN CASE WHEN ((SubPlan 9) < '21'::numeric) THEN 'SINGLE_WITH_CHILD'::text ELSE 'MULTIPLE'::text END ELSE 'No assigned type!'::text END |
| Buffers: shared hit=121 |
| SubPlan 1 |
| -> GroupAggregate (cost=0.00..1.29 rows=1 width=12) (actual time=0.007..0.007 rows=1 loops=22) |
| Output: count(c2.case_id), c2.case_id |
| Group Key: c2.case_id |
| Buffers: shared hit=22 |
| -> Seq Scan on public.cas c2 (cost=0.00..1.27 rows=1 width=4) (actual time=0.002..0.004 rows=3 loops=22) |
| Output: c2.case_id, c2.cat, c2.birth, c2.c_type |
| Filter: (c2.case_id = c1.case_id) |
| Rows Removed by Filter: 19 |
| Buffers: shared hit=22 |
| SubPlan 2 |
| -> GroupAggregate (cost=0.00..1.29 rows=1 width=12) (actual time=0.006..0.006 rows=1 loops=20) |
| Output: count(c2_1.case_id), c2_1.case_id |
| Group Key: c2_1.case_id |
| Buffers: shared hit=20 |
| -> Seq Scan on public.cas c2_1 (cost=0.00..1.27 rows=1 width=4) (actual time=0.002..0.003 rows=4 loops=20) |
| Output: c2_1.case_id, c2_1.cat, c2_1.birth, c2_1.c_type |
| Filter: (c2_1.case_id = c1.case_id) |
| Rows Removed by Filter: 18 |
| Buffers: shared hit=20 |
| SubPlan 3 |
| -> GroupAggregate (cost=0.00..1.29 rows=1 width=12) (actual time=0.006..0.006 rows=1 loops=4) |
| Output: sum(CASE WHEN (c3.cat = 'WIFE'::text) THEN 1 ELSE 0 END), c3.case_id |
| Group Key: c3.case_id |
| Buffers: shared hit=4 |
| -> Seq Scan on public.cas c3 (cost=0.00..1.27 rows=1 width=36) (actual time=0.002..0.003 rows=2 loops=4) |
| Output: c3.case_id, c3.cat, c3.birth, c3.c_type |
| Filter: (c3.case_id = c1.case_id) |
| Rows Removed by Filter: 20 |
| Buffers: shared hit=4 |
| SubPlan 4 |
| -> GroupAggregate (cost=0.00..1.29 rows=1 width=12) (actual time=0.006..0.006 rows=1 loops=18) |
| Output: count(c2_2.case_id), c2_2.case_id |
| Group Key: c2_2.case_id |
| Buffers: shared hit=18 |
| -> Seq Scan on public.cas c2_2 (cost=0.00..1.27 rows=1 width=4) (actual time=0.002..0.003 rows=4 loops=18) |
| Output: c2_2.case_id, c2_2.cat, c2_2.birth, c2_2.c_type |
| Filter: (c2_2.case_id = c1.case_id) |
| Rows Removed by Filter: 18 |
| Buffers: shared hit=18 |
| SubPlan 5 |
| -> GroupAggregate (cost=0.00..1.29 rows=1 width=12) (actual time=0.006..0.006 rows=1 loops=16) |
| Output: sum(CASE WHEN (c3_1.cat = 'WIFE'::text) THEN 1 ELSE 0 END), c3_1.case_id |
| Group Key: c3_1.case_id |
| Buffers: shared hit=16 |
| -> Seq Scan on public.cas c3_1 (cost=0.00..1.27 rows=1 width=36) (actual time=0.002..0.003 rows=4 loops=16) |
| Output: c3_1.case_id, c3_1.cat, c3_1.birth, c3_1.c_type |
| Filter: (c3_1.case_id = c1.case_id) |
| Rows Removed by Filter: 18 |
| Buffers: shared hit=16 |
| SubPlan 6 |
| -> GroupAggregate (cost=0.00..1.30 rows=1 width=36) (actual time=0.010..0.010 rows=1 loops=7) |
| Output: min(EXTRACT(YEAR FROM age((CURRENT_DATE)::timestamp with time zone, (c4.birth)::timestamp with time zone))), c4.case_id |
| Group Key: c4.case_id |
| Buffers: shared hit=7 |
| -> Seq Scan on public.cas c4 (cost=0.00..1.27 rows=1 width=8) (actual time=0.003..0.003 rows=4 loops=7) |
| Output: c4.case_id, c4.cat, c4.birth, c4.c_type |
| Filter: (c4.case_id = c1.case_id) |
| Rows Removed by Filter: 18 |
| Buffers: shared hit=7 |
| SubPlan 7 |
| -> GroupAggregate (cost=0.00..1.29 rows=1 width=12) (actual time=0.006..0.006 rows=1 loops=11) |
| Output: count(c2_3.case_id), c2_3.case_id |
| Group Key: c2_3.case_id |
| Buffers: shared hit=11 |
| -> Seq Scan on public.cas c2_3 (cost=0.00..1.27 rows=1 width=4) (actual time=0.002..0.003 rows=4 loops=11) |
| Output: c2_3.case_id, c2_3.cat, c2_3.birth, c2_3.c_type |
| Filter: (c2_3.case_id = c1.case_id) |
| Rows Removed by Filter: 18 |
| Buffers: shared hit=11 |
| SubPlan 8 |
| -> GroupAggregate (cost=0.00..1.29 rows=1 width=12) (actual time=0.006..0.006 rows=1 loops=11) |
| Output: sum(CASE WHEN (c3_2.cat = 'WIFE'::text) THEN 1 ELSE 0 END), c3_2.case_id |
| Group Key: c3_2.case_id |
| Buffers: shared hit=11 |
| -> Seq Scan on public.cas c3_2 (cost=0.00..1.27 rows=1 width=36) (actual time=0.002..0.003 rows=4 loops=11) |
| Output: c3_2.case_id, c3_2.cat, c3_2.birth, c3_2.c_type |
| Filter: (c3_2.case_id = c1.case_id) |
| Rows Removed by Filter: 18 |
| Buffers: shared hit=11 |
| SubPlan 9 |
| -> GroupAggregate (cost=0.00..1.30 rows=1 width=36) (actual time=0.011..0.011 rows=1 loops=11) |
| Output: min(EXTRACT(YEAR FROM age((CURRENT_DATE)::timestamp with time zone, (c4_1.birth)::timestamp with time zone))), c4_1.case_id |
| Group Key: c4_1.case_id |
| Buffers: shared hit=11 |
| -> Seq Scan on public.cas c4_1 (cost=0.00..1.27 rows=1 width=8) (actual time=0.002..0.003 rows=4 loops=11) |
| Output: c4_1.case_id, c4_1.cat, c4_1.birth, c4_1.c_type |
| Filter: (c4_1.case_id = c1.case_id) |
| Rows Removed by Filter: 18 |
| Buffers: shared hit=11 |
| Planning Time: 0.571 ms |
| Execution Time: 1.068 ms |