-- drop table three;
-- drop table two;
-- drop table one;
-- create table one (acol int PRIMARY KEY, bcol int );
-- create table two (acol int references one (acol), dcol int, ecol int, PRIMARY KEY (acol, dcol));
-- create table three (acol int, dcol int, gcol int, hcol int, PRIMARY KEY(acol, dcol, gcol)
-- , foreign key(acol, dcol) references two (acol, dcol));
-- 
-- insert into one values (1,1);
-- insert into one values (2,1);
-- insert into one values (3,1);
-- 
-- insert into two values (1, 1, 1);
-- insert into two values (2, 1, 2);
-- insert into two values (1, 2, 1);
-- insert into two values (2, 2, 2);
-- insert into two values (1, 3, 1);
-- insert into two values (2, 3, 2);
-- 
-- insert into three values (1, 1, 1, 1);
-- insert into three values (1, 1, 2, 1);
-- insert into three values (1, 2, 1, 1);
-- insert into three values (1, 2, 2, 1);
-- insert into three values (1, 3, 1, 1);
-- insert into three values (1, 3, 2, 1);
-- insert into three values (2, 1, 1, 1);
-- insert into three values (2, 2, 2, 1);
-- insert into three values (2, 3, 1, 1);
-- insert into three values (2, 3, 2, 1);

--
-- original query 1
--
-- EXPLAIN ANALYZE 
SELECT DISTINCT acol, bcol, dcol, ecol
FROM (
	SELECT DISTINCT acol, bcol, dcol, ecol
	FROM ONE o 
	INNER JOIN two t USING (acol )
) AS foo
INNER JOIN three th USING (acol, dcol);

--
-- improved query
-- EXPLAIN ANALYZE 
SELECT DISTINCT acol, bcol, dcol, ecol
FROM one o
INNER JOIN two t USING (acol)
INNER JOIN three th USING (acol, dcol);

--
-- original query 2
--
\echo query 2 original
-- EXPLAIN ANALYZE
SELECT acol, dcol, sum(count_one) AS count_one, sum(count_two) AS count_two, count(DISTINCT gcol) AS gcol_count
FROM (
	SELECT acol, dcol, gcol,
      CASE WHEN gcol = 1 THEN cnt ELSE 0 END AS count_one,
      CASE WHEN gcol = 2 THEN cnt ELSE 0 END AS count_two
   FROM (
      SELECT acol, dcol, count(*) AS cnt, gcol
      FROM one o 
      INNER JOIN two t USING (acol)
      INNER JOIN three the USING (acol, dcol)
      GROUP BY acol, dcol, gcol
   ) AS bar
) AS foo
GROUP BY acol, dcol;

--
-- improved query
--
\echo query 2 improved
EXPLAIN ANALYZE
SELECT acol, dcol, sum(count_one) AS count_one, sum(count_two) AS count_two, count(DISTINCT gcol) AS gcol_count
FROM (
	SELECT acol, dcol, gcol,
      CASE WHEN gcol = 1 THEN count(*) ELSE 0 END AS count_one,
      CASE WHEN gcol = 2 THEN count(*) ELSE 0 END AS count_two
   FROM one o 
   INNER JOIN two t USING (acol)
   INNER JOIN three the USING (acol, dcol)
   GROUP BY acol, dcol, gcol
) AS foo
GROUP BY acol, dcol;


\echo bad query 2 
EXPLAIN ANALYZE
SELECT acol, dcol, 
   (CASE WHEN gcol = 1 THEN count(*) ELSE 0 END) AS count_one,
   (CASE WHEN gcol = 2 THEN count(*) ELSE 0 END) AS count_two,
   count( gcol ) as gcol_count
FROM one o 
INNER JOIN two t USING (acol)
INNER JOIN three th USING (acol, dcol)
GROUP BY acol, dcol, gcol;