Postgres 14

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:

-- 
-- The logic is that  if the COUNT of case_id is = 2 **and** there's a 'WIFE', then
-- it must be a couple with no children - 'PAIR_NO_CHILD'.
--


    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:

--
-- Here we look at where the COUNT of 'case_id's is >= 3. There may or may not
-- be children - so we again check for the exisence of a wife and if there is
-- one, then we check the age of the youngest child (MIN...) - the requirements
-- state that if even 1 child is < 21, then the case_id is classified as 
-- 'PAIR_WITH_CHILD' and if no child is under 21, then it's 'OTHER'
--

    WHEN COUNT(case_id)                             OVER (PARTITION BY case_id) >= 3
    AND  COUNT(case_id) FILTER (WHERE cat = 'WIFE') OVER (PARTITION BY case_id) = 1
      THEN
        CASE
          WHEN MIN(EXTRACT('YEAR' FROM AGE(birth))) OVER (PARTITION BY case_id) < 21
            THEN 'PAIR_WITH_CHILD'
          ELSE 'OTHER'
        END

  
-- 4th section:

-- 
-- If the COUNT of 'case_id' is > 1 (or >= 2...) AND there's no wife, then the
-- others with that 'case_id' **must** be children. Then, with the 'CASE', we
-- determine the age of the youngest child. According to the requirements, having
-- one child < 21, means that all children are counted (and the type is 
-- 'SINGLE_WITH_CHILD' - but if **all** of them are <= 21, then the type is 'MULTIPLE'
--


    WHEN COUNT(case_id)                             OVER (PARTITION BY case_id) >= 2
    AND  COUNT(case_id) FILTER (WHERE cat = 'WIFE') OVER (PARTITION BY case_id) = 0
      THEN
        CASE
          WHEN MIN(EXTRACT('YEAR' FROM AGE(birth))) OVER (PARTITION BY case_id) < 21
            THEN 'SINGLE_WITH_CHILD'
          ELSE 'MULTIPLE'
        END

    ELSE 'No assigned type!'  -- ALWAYS!! have a default - helps to keep track of where 
                              -- you may have missed a case!
  END

-- 5th section: 

FROM
  cas
ORDER BY 
  case_id,
  CASE
    WHEN cat = 'CASE_OWNER' THEN 1
    WHEN cat = 'WIFE'       THEN 2
    WHEN cat = 'CHILD'      THEN 3
  END,
  birth DESC;
  


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
          WHEN s.a_min < 21
            THEN 'PAIR_WITH_CHILD'
          ELSE 'OTHER'
        END

  
-- 4th section:

    WHEN s.c_cnt >= 2 AND s.w_cnt = 0
      THEN
        CASE
          WHEN s.a_min < 21
            THEN 'SINGLE_WITH_CHILD'
          ELSE 'MULTIPLE'
        END

    ELSE 'No assigned type!'  -- ALWAYS!! have a default - helps to keep track of where 
                              -- you may have missed a case!
  END

-- 5th section: 

FROM
(
  SELECT 
    case_id,
    cat, 
    birth,
    (SELECT COUNT(c2.case_id) 
       FROM cas c2 WHERE c2.case_id = c1.case_id GROUP BY c2.case_id) AS c_cnt,
    (SELECT SUM(CASE WHEN c3.cat = 'WIFE' THEN 1 ELSE 0 END)     
       FROM cas c3 WHERE c3.case_id = c1.case_id GROUP BY c3.case_id) AS w_cnt,
       
   (SELECT MIN(EXTRACT('YEAR' 
    FROM AGE(c4.birth))) FROM cas c4 WHERE c4.case_id = c1.case_id GROUP BY c4.case_id) AS a_min      
  FROM cas c1
  ORDER BY 
    c1.case_id,
    CASE
      WHEN c1.cat = 'CASE_OWNER' THEN 1
      WHEN c1.cat = 'WIFE'       THEN 2
      WHEN c1.cat = 'CHILD'      THEN 3
    END,
  c1.birth DESC
) AS s; 



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:


    WHEN COUNT(case_id)                             OVER (PARTITION BY case_id) >= 3
    AND  COUNT(case_id) FILTER (WHERE cat = 'WIFE') OVER (PARTITION BY case_id) = 1
      THEN
        CASE
          WHEN MIN(EXTRACT('YEAR' FROM AGE(birth))) OVER (PARTITION BY case_id) < 21
            THEN 'PAIR_WITH_CHILD'
          ELSE 'OTHER'
        END

  
-- 4th section:



    WHEN COUNT(case_id)                             OVER (PARTITION BY case_id) >= 2
    AND  COUNT(case_id) FILTER (WHERE cat = 'WIFE') OVER (PARTITION BY case_id) = 0
      THEN
        CASE
          WHEN MIN(EXTRACT('YEAR' FROM AGE(birth))) OVER (PARTITION BY case_id) < 21
            THEN 'SINGLE_WITH_CHILD'
          ELSE 'MULTIPLE'
        END

    ELSE 'No assigned type!'  -- ALWAYS!! have a default - helps to keep track of where 
                              -- you may have missed a case!
  END

-- 5th section: 

FROM
(
  SELECT 
    case_id,
    cat, 
    birth,
    (SELECT COUNT(c2.case_id) 
       FROM cas c2 WHERE c2.case_id = c1.case_id GROUP BY c2.case_id) AS c_cnt,
    (SELECT SUM(CASE WHEN c3.cat = 'WIFE' THEN 1 ELSE 0 END)     
       FROM cas c3 WHERE c3.case_id = c1.case_id GROUP BY c3.case_id) AS w_cnt,
       
   (SELECT MIN(EXTRACT('YEAR' 
    FROM AGE(c4.birth))) FROM cas c4 WHERE c4.case_id = c1.case_id GROUP BY c4.case_id) AS a_min      
  FROM cas c1
  ORDER BY 
    c1.case_id,
    CASE
      WHEN c1.cat = 'CASE_OWNER' THEN 1
      WHEN c1.cat = 'WIFE'       THEN 2
      WHEN c1.cat = 'CHILD'      THEN 3
    END,
  c1.birth DESC
) AS s; 


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
-- it must be a couple with no children - 'PAIR_NO_CHILD'.
--


    WHEN s.c_cnt = 2 AND s.w_cnt = 1  
      THEN 'PAIR_NO_CHILD'


-- 3rd section:

--
-- Here we look at where the COUNT of 'case_id's is >= 3. There may or may not
-- be children - so we again check for the exisence of a wife and if there is
-- one, then we check the age of the youngest child (MIN...) - the requirements
-- state that if even 1 child is < 21, then the case_id is classified as 
-- 'PAIR_WITH_CHILD' and if no child is under 21, then it's 'OTHER'
--

    WHEN s.c_cnt >= 3 AND  s.w_cnt = 1
      THEN
        CASE
          WHEN s.a_min < 21
            THEN 'PAIR_WITH_CHILD'
          ELSE 'OTHER'
        END

  
-- 4th section:

-- 
-- If the COUNT of 'case_id' is > 1 (or >= 2...) AND there's no wife, then the
-- others with that 'case_id' **must** be children. Then, with the 'CASE', we
-- determine the age of the youngest child. According to the requirements, having
-- one child < 21, means that all children are counted (and the type is 
-- 'SINGLE_WITH_CHILD' - but if **all** of them are <= 21, then the type is 'MULTIPLE'
--


    WHEN s.c_cnt >= 2 AND s.w_cnt = 0
      THEN
        CASE
          WHEN s.a_min < 21
            THEN 'SINGLE_WITH_CHILD'
          ELSE 'MULTIPLE'
        END

    ELSE 'No assigned type!'  -- ALWAYS!! have a default - helps to keep track of where 
                              -- you may have missed a case!
  END

-- 5th section: 

FROM
(
  SELECT 
    case_id,
    cat, 
    birth,
    (SELECT COUNT(c2.case_id) 
       FROM cas c2 WHERE c2.case_id = c1.case_id GROUP BY c2.case_id) AS c_cnt,
    (SELECT SUM(CASE WHEN c3.cat = 'WIFE' THEN 1 ELSE 0 END)     
       FROM cas c3 WHERE c3.case_id = c1.case_id GROUP BY c3.case_id) AS w_cnt,
       
   (SELECT MIN(EXTRACT('YEAR' 
    FROM AGE(c4.birth))) FROM cas c4 WHERE c4.case_id = c1.case_id GROUP BY c4.case_id) AS a_min      
  FROM cas c1
  ORDER BY 
    c1.case_id,
    CASE
      WHEN c1.cat = 'CASE_OWNER' THEN 1
      WHEN c1.cat = 'WIFE'       THEN 2
      WHEN c1.cat = 'CHILD'      THEN 3
    END,
  c1.birth DESC
) AS s; 





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:

-- 
-- The logic is that  if the COUNT of case_id is = 2 **and** there's a 'WIFE', then
-- it must be a couple with no children - 'PAIR_NO_CHILD'.
--


    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:

--
-- Here we look at where the COUNT of 'case_id's is >= 3. There may or may not
-- be children - so we again check for the exisence of a wife and if there is
-- one, then we check the age of the youngest child (MIN...) - the requirements
-- state that if even 1 child is < 21, then the case_id is classified as 
-- 'PAIR_WITH_CHILD' and if no child is under 21, then it's 'OTHER'
--

    WHEN COUNT(case_id)                             OVER (PARTITION BY case_id) >= 3
    AND  COUNT(case_id) FILTER (WHERE cat = 'WIFE') OVER (PARTITION BY case_id) = 1
      THEN
        CASE
          WHEN MIN(EXTRACT('YEAR' FROM AGE(birth))) OVER (PARTITION BY case_id) < 21
            THEN 'PAIR_WITH_CHILD'
          ELSE 'OTHER'
        END

  
-- 4th section:

-- 
-- If the COUNT of 'case_id' is > 1 (or >= 2...) AND there's no wife, then the
-- others with that 'case_id' **must** be children. Then, with the 'CASE', we
-- determine the age of the youngest child. According to the requirements, having
-- one child < 21, means that all children are counted (and the type is 
-- 'SINGLE_WITH_CHILD' - but if **all** of them are <= 21, then the type is 'MULTIPLE'
--


    WHEN COUNT(case_id)                             OVER (PARTITION BY case_id) >= 2
    AND  COUNT(case_id) FILTER (WHERE cat = 'WIFE') OVER (PARTITION BY case_id) = 0
      THEN
        CASE
          WHEN MIN(EXTRACT('YEAR' FROM AGE(birth))) OVER (PARTITION BY case_id) < 21
            THEN 'SINGLE_WITH_CHILD'
          ELSE 'MULTIPLE'
        END

    ELSE 'No assigned type!'  -- ALWAYS!! have a default - helps to keep track of where 
                              -- you may have missed a case!
  END

-- 5th section: 

FROM
  cas;
 


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
        CASE
          WHEN s.a_min < 21 
            THEN 'SINGLE_WITH_CHILD'
          ELSE 'MULTIPLE'
        END         
        
    ELSE 'No assigned type!'
  END AS c_t
FROM
(
  SELECT 
    case_id,
    cat, 
    birth,
    (SELECT COUNT(c2.case_id) 
       FROM cas c2 WHERE c2.case_id = c1.case_id GROUP BY c2.case_id) AS c_cnt,
    (SELECT SUM(CASE WHEN c3.cat = 'WIFE' THEN 1 ELSE 0 END)     
       FROM cas c3 WHERE c3.case_id = c1.case_id GROUP BY c3.case_id) AS w_cnt,
       
   (SELECT MIN(EXTRACT('YEAR' 
    FROM AGE(c4.birth))) FROM cas c4 WHERE c4.case_id = c1.case_id GROUP BY c4.case_id) AS a_min      
  FROM cas c1
) AS s;




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:


    WHEN COUNT(case_id)                             OVER (PARTITION BY case_id) >= 3
    AND  COUNT(case_id) FILTER (WHERE cat = 'WIFE') OVER (PARTITION BY case_id) = 1
      THEN
        CASE
          WHEN MIN(EXTRACT('YEAR' FROM AGE(birth))) OVER (PARTITION BY case_id) < 21
            THEN 'PAIR_WITH_CHILD'
          ELSE 'OTHER'
        END

  
-- 4th section:



    WHEN COUNT(case_id)                             OVER (PARTITION BY case_id) >= 2
    AND  COUNT(case_id) FILTER (WHERE cat = 'WIFE') OVER (PARTITION BY case_id) = 0
      THEN
        CASE
          WHEN MIN(EXTRACT('YEAR' FROM AGE(birth))) OVER (PARTITION BY case_id) < 21
            THEN 'SINGLE_WITH_CHILD'
          ELSE 'MULTIPLE'
        END

    ELSE 'No assigned type!'  -- ALWAYS!! have a default - helps to keep track of where 
                              -- you may have missed a case!
  END

-- 5th section: 

FROM
(
  SELECT 
    case_id,
    cat, 
    birth,
    (SELECT COUNT(c2.case_id) 
       FROM cas c2 WHERE c2.case_id = c1.case_id GROUP BY c2.case_id) AS c_cnt,
    (SELECT SUM(CASE WHEN c3.cat = 'WIFE' THEN 1 ELSE 0 END)     
       FROM cas c3 WHERE c3.case_id = c1.case_id GROUP BY c3.case_id) AS w_cnt,
       
   (SELECT MIN(EXTRACT('YEAR' 
    FROM AGE(c4.birth))) FROM cas c4 WHERE c4.case_id = c1.case_id GROUP BY c4.case_id) AS a_min      
  FROM cas c1
  ORDER BY 
    c1.case_id,
    CASE
      WHEN c1.cat = 'CASE_OWNER' THEN 1
      WHEN c1.cat = 'WIFE'       THEN 2
      WHEN c1.cat = 'CHILD'      THEN 3
    END,
  c1.birth DESC
) AS s; 


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
        CASE
          WHEN s.a_min < 21 
            THEN 'SINGLE_WITH_CHILD'
          ELSE 'MULTIPLE'
        END         
        
    ELSE 'No assigned type!'
  END AS c_t
FROM
(
  SELECT 
    case_id,
    cat, 
    birth,
    (SELECT COUNT(c2.case_id) 
       FROM cas c2 WHERE c2.case_id = c1.case_id GROUP BY c2.case_id) AS c_cnt,
    (SELECT SUM(CASE WHEN c3.cat = 'WIFE' THEN 1 ELSE 0 END)     
       FROM cas c3 WHERE c3.case_id = c1.case_id GROUP BY c3.case_id) AS w_cnt,
       
   (SELECT MIN(EXTRACT('YEAR' 
    FROM AGE(c4.birth))) FROM cas c4 WHERE c4.case_id = c1.case_id GROUP BY c4.case_id) AS a_min      
  FROM cas c1
) AS s;




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
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