-- Complements in the count pushdown (DESIGN.md §35, "Complements in an -- OR"): `IS DISTINCT FROM`, `NOT` of a multi-key query, and arms of an OR -- that are `<>`, `IS NOT NULL`, `IS DISTINCT FROM`, `NOT IN` or such a `NOT`, -- counted by De Morgan over the rows every count is handed. Each query is -- run through the LionCount node, with every other scan disabled so that a -- node that is built at all is the plan, and again as a sequential scan with -- the pushdown off; the two answers are compared as multisets both ways -- round. The table has NULLs, negative values, empty arrays and NULL arrays, -- and rows deleted and updated after its VACUUM. \set VERBOSITY terse SET client_min_messages = warning; CREATE EXTENSION IF NOT EXISTS pg_lion; RESET client_min_messages; SET synchronous_commit = on; SET max_parallel_workers_per_gather = 0; CREATE TABLE cc (id int, a int, b int NOT NULL, n int, t text, tags text[]) WITH (autovacuum_enabled = off); INSERT INTO cc SELECT i, i % 7, i % 5, CASE WHEN i % 4 = 0 THEN NULL ELSE i % 11 - 5 END, CASE WHEN i % 9 = 0 THEN NULL ELSE 'v' || (i % 13) END, CASE WHEN i % 10 = 0 THEN NULL WHEN i % 10 = 1 THEN '{}'::text[] WHEN i % 10 = 2 THEN ARRAY['t' || (i % 6), NULL] ELSE ARRAY['t' || (i % 6), 't' || (i % 17)] END FROM generate_series(1, 20000) i; CREATE INDEX cc_a ON cc USING lion (a); CREATE INDEX cc_b ON cc USING lion (b); CREATE INDEX cc_n ON cc USING lion (n); CREATE INDEX cc_t ON cc USING lion (t); CREATE INDEX cc_tags ON cc USING lion (tags); VACUUM (FREEZE, ANALYZE) cc; -- dead and updated rows the visibility map no longer covers DELETE FROM cc WHERE id % 97 = 0; UPDATE cc SET a = (a + 1) % 7, n = NULL WHERE id % 89 = 0; UPDATE cc SET n = -2, tags = '{t5}' WHERE id % 83 = 0; /* * cc_q() runs q through the LionCount node and as a sequential scan with the * pushdown off, and compares the two: 'count, N rows' when the node was used * and they agree. */ CREATE FUNCTION cc_q(q text) RETURNS text LANGUAGE plpgsql AS $$ DECLARE ln text; used boolean := false; nrows bigint; ndiff bigint; BEGIN PERFORM set_config('enable_seqscan', 'off', true); PERFORM set_config('enable_bitmapscan', 'off', true); PERFORM set_config('pg_lion.enable_plain_scan', 'off', true); PERFORM set_config('pg_lion.enable_count_pushdown', 'on', true); FOR ln IN EXECUTE 'EXPLAIN (COSTS OFF) ' || q LOOP IF ln ~ 'Custom Scan \(LionCount\)' THEN used := true; END IF; END LOOP; EXECUTE format('CREATE TEMP TABLE cc_on AS SELECT s::text AS r FROM (%s) s', q); PERFORM set_config('enable_seqscan', 'on', true); PERFORM set_config('pg_lion.enable_count_pushdown', 'off', true); EXECUTE format('CREATE TEMP TABLE cc_off AS SELECT s::text AS r FROM (%s) s', q); PERFORM set_config('enable_bitmapscan', 'on', true); PERFORM set_config('pg_lion.enable_plain_scan', 'on', true); PERFORM set_config('pg_lion.enable_count_pushdown', 'on', true); EXECUTE 'SELECT count(*) FROM cc_on' INTO nrows; EXECUTE 'SELECT (SELECT count(*) FROM (SELECT * FROM cc_on EXCEPT ALL SELECT * FROM cc_off) a)' ' + (SELECT count(*) FROM (SELECT * FROM cc_off EXCEPT ALL SELECT * FROM cc_on) b)' INTO ndiff; EXECUTE 'DROP TABLE cc_on, cc_off'; IF ndiff <> 0 THEN RETURN format('MISMATCH: %s rows differ', ndiff); END IF; RETURN format('%s, %s rows', CASE WHEN used THEN 'count' ELSE 'no count' END, nrows); END $$; -- the plan, with the node the only scan allowed CREATE FUNCTION cc_plan(q text) RETURNS SETOF text LANGUAGE plpgsql AS $$ DECLARE ln text; BEGIN PERFORM set_config('enable_seqscan', 'off', true); PERFORM set_config('enable_bitmapscan', 'off', true); PERFORM set_config('pg_lion.enable_plain_scan', 'off', true); FOR ln IN EXECUTE 'EXPLAIN (COSTS OFF) ' || q LOOP RETURN NEXT ln; END LOOP; PERFORM set_config('enable_seqscan', 'on', true); PERFORM set_config('enable_bitmapscan', 'on', true); PERFORM set_config('pg_lion.enable_plain_scan', 'on', true); END $$; -- ---- the plans ---- SELECT cc_plan('SELECT count(*) FROM cc WHERE n IS DISTINCT FROM -2'); cc_plan -------------------------------------------------------------------------- Custom Scan (LionCount) Lion Indexes: cc_b (all rows), cc_n (n IS DISTINCT FROM '-2'::integer) (2 rows) SELECT cc_plan('SELECT count(*) FROM cc WHERE n <> -2 OR n IS NULL'); cc_plan ----------------------------------------------------------------------------------- Custom Scan (LionCount) Lion Indexes: cc_b (all rows), cc_n, cc_n ((n <> '-2'::integer) OR (n IS NULL)) (2 rows) SELECT cc_plan('SELECT count(*) FROM cc WHERE a = 2 OR n <> -2'); cc_plan ------------------------------------------------------------------------------- Custom Scan (LionCount) Lion Indexes: cc_b (all rows), cc_a, cc_n ((a = 2) OR (n <> '-2'::integer)) (2 rows) SELECT cc_plan('SELECT count(*) FROM cc WHERE NOT (tags @> ''{t3}'')'); cc_plan ------------------------------------------------------------------------- Custom Scan (LionCount) Lion Indexes: cc_b (all rows), cc_tags (NOT (tags @> '{t3}'::text[])) (2 rows) SELECT cc_plan('SELECT a, count(*) FROM cc WHERE b = 1 AND (n <> 3 OR t = ''v4'') GROUP BY a'); cc_plan ----------------------------------------------------------------------------------- Custom Scan (LionCount) Lion Indexes: cc_a (a), cc_n, cc_t ((n <> 3) OR (t = 'v4'::text)), cc_b (b = 1) Group Key: a (3 rows) -- ---- IS DISTINCT FROM, and NOT of a multi-key query, alone ---- SELECT q, cc_q(q) FROM (VALUES ('SELECT count(*) FROM cc WHERE n IS DISTINCT FROM -2'), ('SELECT count(*) FROM cc WHERE -2 IS DISTINCT FROM n'), ('SELECT count(*) FROM cc WHERE n IS DISTINCT FROM 99'), ('SELECT count(*) FROM cc WHERE n IS DISTINCT FROM NULL'), ('SELECT count(*) FROM cc WHERE n IS DISTINCT FROM 3::bigint'), ('SELECT count(*) FROM cc WHERE t IS DISTINCT FROM ''v4'''), ('SELECT count(*) FROM cc WHERE n IS DISTINCT FROM 3 AND a = 2'), ('SELECT count(*) FROM cc WHERE n IS DISTINCT FROM 3 AND t IS DISTINCT FROM ''v1'''), ('SELECT count(n), count(t) FROM cc WHERE n IS DISTINCT FROM 3'), ('SELECT a, count(*) FROM cc WHERE n IS DISTINCT FROM 3 GROUP BY a'), ('SELECT n, count(*) FROM cc WHERE n IS DISTINCT FROM 3 GROUP BY n'), ('SELECT n, count(*) FROM cc WHERE n IS DISTINCT FROM 3 GROUP BY n ORDER BY count(*) DESC LIMIT 3'), ('SELECT count(DISTINCT n) FROM cc WHERE n IS DISTINCT FROM 3'), ('SELECT count(*) FROM cc WHERE NOT (tags @> ''{t3}'')'), ('SELECT count(*) FROM cc WHERE NOT (tags @> ''{t3,t5}'')'), ('SELECT count(*) FROM cc WHERE NOT (tags && ''{t1,t16}'')'), ('SELECT count(*) FROM cc WHERE NOT (tags @> ''{nope}'')'), ('SELECT count(*), count(tags) FROM cc WHERE NOT (tags @> ''{t3}'') AND b = 2'), ('SELECT b, count(*) FROM cc WHERE NOT (tags @> ''{t3}'') GROUP BY b') ) v(q); q | cc_q -------------------------------------------------------------------------------------------------+------------------ SELECT count(*) FROM cc WHERE n IS DISTINCT FROM -2 | count, 1 rows SELECT count(*) FROM cc WHERE -2 IS DISTINCT FROM n | count, 1 rows SELECT count(*) FROM cc WHERE n IS DISTINCT FROM 99 | count, 1 rows SELECT count(*) FROM cc WHERE n IS DISTINCT FROM NULL | count, 1 rows SELECT count(*) FROM cc WHERE n IS DISTINCT FROM 3::bigint | count, 1 rows SELECT count(*) FROM cc WHERE t IS DISTINCT FROM 'v4' | count, 1 rows SELECT count(*) FROM cc WHERE n IS DISTINCT FROM 3 AND a = 2 | count, 1 rows SELECT count(*) FROM cc WHERE n IS DISTINCT FROM 3 AND t IS DISTINCT FROM 'v1' | count, 1 rows SELECT count(n), count(t) FROM cc WHERE n IS DISTINCT FROM 3 | no count, 1 rows SELECT a, count(*) FROM cc WHERE n IS DISTINCT FROM 3 GROUP BY a | count, 7 rows SELECT n, count(*) FROM cc WHERE n IS DISTINCT FROM 3 GROUP BY n | count, 11 rows SELECT n, count(*) FROM cc WHERE n IS DISTINCT FROM 3 GROUP BY n ORDER BY count(*) DESC LIMIT 3 | count, 3 rows SELECT count(DISTINCT n) FROM cc WHERE n IS DISTINCT FROM 3 | count, 1 rows SELECT count(*) FROM cc WHERE NOT (tags @> '{t3}') | count, 1 rows SELECT count(*) FROM cc WHERE NOT (tags @> '{t3,t5}') | count, 1 rows SELECT count(*) FROM cc WHERE NOT (tags && '{t1,t16}') | count, 1 rows SELECT count(*) FROM cc WHERE NOT (tags @> '{nope}') | count, 1 rows SELECT count(*), count(tags) FROM cc WHERE NOT (tags @> '{t3}') AND b = 2 | count, 1 rows SELECT b, count(*) FROM cc WHERE NOT (tags @> '{t3}') GROUP BY b | count, 5 rows (19 rows) -- ---- complements in the arms of an OR ---- SELECT q, cc_q(q) FROM (VALUES ('SELECT count(*) FROM cc WHERE n <> -2 OR n IS NULL'), ('SELECT count(*) FROM cc WHERE n IS NULL OR n <> -2'), ('SELECT count(*) FROM cc WHERE a = 2 OR n <> -2'), ('SELECT count(*) FROM cc WHERE n <> -2 OR a = 2 OR t = ''v1'''), ('SELECT count(*) FROM cc WHERE n IS NOT NULL OR t = ''v1'''), ('SELECT count(*) FROM cc WHERE n IS DISTINCT FROM 3 OR a = 1'), ('SELECT count(*) FROM cc WHERE n <> 3 OR t <> ''v1'''), ('SELECT count(*) FROM cc WHERE n <> 3 OR t IS NOT NULL'), ('SELECT count(*) FROM cc WHERE (n <> 3 AND t <> ''v1'') OR a = 4'), ('SELECT count(*) FROM cc WHERE n NOT IN (1, 2, 3) OR a = 4'), ('SELECT count(*) FROM cc WHERE n <> 99 OR a = 4'), ('SELECT count(*) FROM cc WHERE (a = 1 AND b = 2) OR n <> 3'), ('SELECT count(*) FROM cc WHERE n < 0 OR n <> 3'), ('SELECT count(*) FROM cc WHERE a IN (1, 2) OR n <> 3'), ('SELECT count(*) FROM cc WHERE NOT (tags @> ''{t3}'') OR a = 1'), ('SELECT count(*) FROM cc WHERE NOT (tags @> ''{t3}'') OR tags IS NULL'), ('SELECT count(*) FROM cc WHERE tags @> ''{t2}'' OR NOT (tags && ''{t3,t4}'')'), ('SELECT count(*) FROM cc WHERE b = 1 AND (n <> 3 OR t = ''v4'')'), ('SELECT count(*) FROM cc WHERE b = 1 AND (n <> 3 OR t = ''v4'') AND (a <> 2 OR n IS NULL)'), ('SELECT count(*) FROM cc WHERE (n <> 3 OR t = ''v4'') AND n IS NOT NULL'), ('SELECT count(n), count(t) FROM cc WHERE n <> 3 OR t = ''v4'''), ('SELECT a, count(*) FROM cc WHERE n <> 3 OR t = ''v4'' GROUP BY a'), ('SELECT a, count(*) FROM cc WHERE b = 1 AND (n <> 3 OR t = ''v4'') GROUP BY a'), ('SELECT n, count(*) FROM cc WHERE n <> 3 OR a = 1 GROUP BY n'), ('SELECT a, b, count(*) FROM cc WHERE n <> 3 OR t = ''v4'' GROUP BY a, b'), ('SELECT a, count(*) FROM cc WHERE n <> 3 OR t = ''v4'' GROUP BY a HAVING count(*) > 2500'), ('SELECT a, count(*) FROM cc WHERE n <> 3 OR t = ''v4'' GROUP BY a ORDER BY count(*) DESC LIMIT 2'), ('SELECT count(DISTINCT a) FROM cc WHERE n <> 3 OR t = ''v4'''), ('SELECT a, count(DISTINCT n) FROM cc WHERE n <> 3 OR t = ''v4'' GROUP BY a'), ('SELECT count(*) FROM cc WHERE (n <> 3 OR t = ''v4'') AND b IN (1, 2, 3)'), ('SELECT count(*) FROM cc WHERE (n <> 3 OR t = ''v4'') AND a >= 2 AND a < 6') ) v(q); q | cc_q -----------------------------------------------------------------------------------------------+------------------ SELECT count(*) FROM cc WHERE n <> -2 OR n IS NULL | count, 1 rows SELECT count(*) FROM cc WHERE n IS NULL OR n <> -2 | count, 1 rows SELECT count(*) FROM cc WHERE a = 2 OR n <> -2 | count, 1 rows SELECT count(*) FROM cc WHERE n <> -2 OR a = 2 OR t = 'v1' | count, 1 rows SELECT count(*) FROM cc WHERE n IS NOT NULL OR t = 'v1' | count, 1 rows SELECT count(*) FROM cc WHERE n IS DISTINCT FROM 3 OR a = 1 | count, 1 rows SELECT count(*) FROM cc WHERE n <> 3 OR t <> 'v1' | count, 1 rows SELECT count(*) FROM cc WHERE n <> 3 OR t IS NOT NULL | count, 1 rows SELECT count(*) FROM cc WHERE (n <> 3 AND t <> 'v1') OR a = 4 | count, 1 rows SELECT count(*) FROM cc WHERE n NOT IN (1, 2, 3) OR a = 4 | count, 1 rows SELECT count(*) FROM cc WHERE n <> 99 OR a = 4 | count, 1 rows SELECT count(*) FROM cc WHERE (a = 1 AND b = 2) OR n <> 3 | count, 1 rows SELECT count(*) FROM cc WHERE n < 0 OR n <> 3 | count, 1 rows SELECT count(*) FROM cc WHERE a IN (1, 2) OR n <> 3 | count, 1 rows SELECT count(*) FROM cc WHERE NOT (tags @> '{t3}') OR a = 1 | count, 1 rows SELECT count(*) FROM cc WHERE NOT (tags @> '{t3}') OR tags IS NULL | count, 1 rows SELECT count(*) FROM cc WHERE tags @> '{t2}' OR NOT (tags && '{t3,t4}') | count, 1 rows SELECT count(*) FROM cc WHERE b = 1 AND (n <> 3 OR t = 'v4') | count, 1 rows SELECT count(*) FROM cc WHERE b = 1 AND (n <> 3 OR t = 'v4') AND (a <> 2 OR n IS NULL) | count, 1 rows SELECT count(*) FROM cc WHERE (n <> 3 OR t = 'v4') AND n IS NOT NULL | count, 1 rows SELECT count(n), count(t) FROM cc WHERE n <> 3 OR t = 'v4' | no count, 1 rows SELECT a, count(*) FROM cc WHERE n <> 3 OR t = 'v4' GROUP BY a | count, 7 rows SELECT a, count(*) FROM cc WHERE b = 1 AND (n <> 3 OR t = 'v4') GROUP BY a | count, 7 rows SELECT n, count(*) FROM cc WHERE n <> 3 OR a = 1 GROUP BY n | count, 12 rows SELECT a, b, count(*) FROM cc WHERE n <> 3 OR t = 'v4' GROUP BY a, b | count, 35 rows SELECT a, count(*) FROM cc WHERE n <> 3 OR t = 'v4' GROUP BY a HAVING count(*) > 2500 | count, 0 rows SELECT a, count(*) FROM cc WHERE n <> 3 OR t = 'v4' GROUP BY a ORDER BY count(*) DESC LIMIT 2 | count, 2 rows SELECT count(DISTINCT a) FROM cc WHERE n <> 3 OR t = 'v4' | count, 1 rows SELECT a, count(DISTINCT n) FROM cc WHERE n <> 3 OR t = 'v4' GROUP BY a | count, 7 rows SELECT count(*) FROM cc WHERE (n <> 3 OR t = 'v4') AND b IN (1, 2, 3) | count, 1 rows SELECT count(*) FROM cc WHERE (n <> 3 OR t = 'v4') AND a >= 2 AND a < 6 | count, 1 rows (31 rows) -- ---- what is declined: an arm that ANDs a complement with a selection, a -- NOT of a query the posting sets only bound, more than three such ORs ---- SELECT q, cc_q(q) FROM (VALUES ('SELECT count(*) FROM cc WHERE (a = 1 AND n <> 3) OR t = ''v4'''), ('SELECT count(*) FROM cc WHERE NOT (tags @> ''{}'')'), ('SELECT count(*) FROM cc WHERE NOT (tags @> ''{t3,NULL}'')'), ('SELECT count(*) FROM cc WHERE (a <> 1 OR b = 1) AND (a <> 2 OR b = 2) AND (a <> 3 OR b = 3) AND (a <> 4 OR b = 4)') ) v(q); q | cc_q -------------------------------------------------------------------------------------------------------------------+------------------ SELECT count(*) FROM cc WHERE (a = 1 AND n <> 3) OR t = 'v4' | no count, 1 rows SELECT count(*) FROM cc WHERE NOT (tags @> '{}') | no count, 1 rows SELECT count(*) FROM cc WHERE NOT (tags @> '{t3,NULL}') | no count, 1 rows SELECT count(*) FROM cc WHERE (a <> 1 OR b = 1) AND (a <> 2 OR b = 2) AND (a <> 3 OR b = 3) AND (a <> 4 OR b = 4) | no count, 1 rows (4 rows) -- ---- parameters: a generic plan, and a NULL value ---- SET plan_cache_mode = force_generic_plan; PREPARE cc_d(int) AS SELECT count(*) FROM cc WHERE n IS DISTINCT FROM $1; PREPARE cc_o(int, int) AS SELECT count(*) FROM cc WHERE a = $1 OR n <> $2; PREPARE cc_g(int) AS SELECT a, count(*) FROM cc WHERE n <> $1 OR n IS NULL GROUP BY a ORDER BY a; SELECT cc_plan('EXECUTE cc_o(2, 3)'); cc_plan --------------------------------------------------------------------- Custom Scan (LionCount) Lion Indexes: cc_b (all rows), cc_a, cc_n ((a = $1) OR (n <> $2)) (2 rows) EXECUTE cc_d(3); count ------- 18477 (1 row) EXECUTE cc_d(NULL); count ------- 14741 (1 row) EXECUTE cc_o(2, 3); count ------- 14333 (1 row) EXECUTE cc_o(2, NULL); count ------- 2829 (1 row) EXECUTE cc_o(NULL, NULL); count ------- 0 (1 row) EXECUTE cc_g(3); a | count ---+------- 0 | 2641 1 | 2640 2 | 2642 3 | 2638 4 | 2640 5 | 2637 6 | 2639 (7 rows) EXECUTE cc_g(NULL); a | count ---+------- 0 | 723 1 | 724 2 | 722 3 | 719 4 | 724 5 | 720 6 | 721 (7 rows) SET pg_lion.enable_count_pushdown = off; EXECUTE cc_d(3); count ------- 18477 (1 row) EXECUTE cc_d(NULL); count ------- 14741 (1 row) EXECUTE cc_o(2, 3); count ------- 14333 (1 row) EXECUTE cc_o(2, NULL); count ------- 2829 (1 row) EXECUTE cc_o(NULL, NULL); count ------- 0 (1 row) EXECUTE cc_g(3); a | count ---+------- 0 | 2641 1 | 2640 2 | 2642 3 | 2638 4 | 2640 5 | 2637 6 | 2639 (7 rows) EXECUTE cc_g(NULL); a | count ---+------- 0 | 723 1 | 724 2 | 722 3 | 719 4 | 724 5 | 720 6 | 721 (7 rows) RESET pg_lion.enable_count_pushdown; DEALLOCATE cc_d; DEALLOCATE cc_o; DEALLOCATE cc_g; RESET plan_cache_mode; -- ---- a LATERAL rescan with a new value each time ---- SELECT cc_q('SELECT v.x, (SELECT count(*) FROM cc WHERE a = v.x OR n <> v.x) FROM (VALUES (1), (2), (3)) v(x)'); cc_q --------------- count, 3 rows (1 row) -- ---- a partitioned table: arms the bounds refute or imply ---- -- ---- complements beside filtered counts (DESIGN.md §10, "Filtered counts") ---- -- in the WHERE, which every run counts, and in a filter's condition, which -- only its own run does SELECT q, cc_q(q) FROM (VALUES ('SELECT count(*) FILTER (WHERE b = 1), count(*) FROM cc WHERE n IS DISTINCT FROM 3'), ('SELECT count(*) FILTER (WHERE b = 1), count(*) FROM cc WHERE a = 2 AND (b = 3 OR n <> 3)'), ('SELECT count(*) FILTER (WHERE n IS DISTINCT FROM 3), count(*) FROM cc WHERE a = 2'), ('SELECT count(*) FILTER (WHERE b = 1 OR n <> 3), count(*) FILTER (WHERE t = ''v1''), count(*) FROM cc WHERE a = 2'), ('SELECT sum(CASE WHEN NOT (tags @> ''{t1}'') THEN 1 ELSE 0 END), count(*) FROM cc WHERE a = 2'), ('SELECT b, count(*) FILTER (WHERE n IS DISTINCT FROM 3), count(*) FROM cc WHERE a = 2 GROUP BY b'), ('SELECT b, count(*) FILTER (WHERE t = ''v1'' OR n <> 3), count(*) FROM cc WHERE a = 2 OR n IS NOT NULL GROUP BY b'), ('SELECT a, b, count(*) FILTER (WHERE n IS DISTINCT FROM 3 OR t = ''v1''), count(*) FROM cc WHERE t IS NOT NULL GROUP BY a, b'), ('SELECT a, b, t, count(*) FILTER (WHERE b = 1 OR n <> 3), count(*) FROM cc GROUP BY a, b, t') ) v(q); q | cc_q ---------------------------------------------------------------------------------------------------------------------------+-------------------- SELECT count(*) FILTER (WHERE b = 1), count(*) FROM cc WHERE n IS DISTINCT FROM 3 | count, 1 rows SELECT count(*) FILTER (WHERE b = 1), count(*) FROM cc WHERE a = 2 AND (b = 3 OR n <> 3) | count, 1 rows SELECT count(*) FILTER (WHERE n IS DISTINCT FROM 3), count(*) FROM cc WHERE a = 2 | count, 1 rows SELECT count(*) FILTER (WHERE b = 1 OR n <> 3), count(*) FILTER (WHERE t = 'v1'), count(*) FROM cc WHERE a = 2 | count, 1 rows SELECT sum(CASE WHEN NOT (tags @> '{t1}') THEN 1 ELSE 0 END), count(*) FROM cc WHERE a = 2 | count, 1 rows SELECT b, count(*) FILTER (WHERE n IS DISTINCT FROM 3), count(*) FROM cc WHERE a = 2 GROUP BY b | count, 5 rows SELECT b, count(*) FILTER (WHERE t = 'v1' OR n <> 3), count(*) FROM cc WHERE a = 2 OR n IS NOT NULL GROUP BY b | count, 5 rows SELECT a, b, count(*) FILTER (WHERE n IS DISTINCT FROM 3 OR t = 'v1'), count(*) FROM cc WHERE t IS NOT NULL GROUP BY a, b | count, 35 rows SELECT a, b, t, count(*) FILTER (WHERE b = 1 OR n <> 3), count(*) FROM cc GROUP BY a, b, t | no count, 490 rows (9 rows) -- beside a sum over every row (DESIGN.md §35): a filter whose only source is -- an OR with a complement arm selects nothing by itself, and is counted by -- the sum as the WHERE's run is, not as an intersection of its own SELECT cc_plan('SELECT count(*), count(*) FILTER (WHERE a <> 1 OR b <> 2) FROM cc'); cc_plan ----------------------------------------------------- Custom Scan (LionCount) Lion Indexes: cc_b (all rows) Count Filter 1: cc_a, cc_b ((a <> 1) OR (b <> 2)) (3 rows) SELECT q, cc_q(q) FROM (VALUES ('SELECT count(*), count(*) FILTER (WHERE a <> 1 OR b <> 2) FROM cc'), ('SELECT count(*), count(*) FILTER (WHERE n <> 3 OR t = ''v1'') FROM cc'), ('SELECT count(*), count(*) FILTER (WHERE n IS DISTINCT FROM 3 OR a = 1), count(*) FILTER (WHERE b = 1) FROM cc'), ('SELECT count(*) FILTER (WHERE a <> 1 OR n IS NULL), count(*) FILTER (WHERE NOT (tags @> ''{t3}'') OR b = 4), count(*) FROM cc WHERE t <> ''v1'''), ('SELECT count(*) FILTER (WHERE (a <> 1 OR b <> 2) AND n IS NOT NULL), count(*) FROM cc WHERE n <> 3') ) v(q); q | cc_q ---------------------------------------------------------------------------------------------------------------------------------------------+--------------- SELECT count(*), count(*) FILTER (WHERE a <> 1 OR b <> 2) FROM cc | count, 1 rows SELECT count(*), count(*) FILTER (WHERE n <> 3 OR t = 'v1') FROM cc | count, 1 rows SELECT count(*), count(*) FILTER (WHERE n IS DISTINCT FROM 3 OR a = 1), count(*) FILTER (WHERE b = 1) FROM cc | count, 1 rows SELECT count(*) FILTER (WHERE a <> 1 OR n IS NULL), count(*) FILTER (WHERE NOT (tags @> '{t3}') OR b = 4), count(*) FROM cc WHERE t <> 'v1' | count, 1 rows SELECT count(*) FILTER (WHERE (a <> 1 OR b <> 2) AND n IS NOT NULL), count(*) FROM cc WHERE n <> 3 | count, 1 rows (5 rows) CREATE TABLE ccp (a int, n int, t text) PARTITION BY RANGE (a); CREATE TABLE ccp1 PARTITION OF ccp FOR VALUES FROM (0) TO (3); CREATE TABLE ccp2 PARTITION OF ccp FOR VALUES FROM (3) TO (7); INSERT INTO ccp SELECT a, n, t FROM cc; CREATE INDEX ccp_a ON ccp USING lion (a); CREATE INDEX ccp_n ON ccp USING lion (n); CREATE INDEX ccp_t ON ccp USING lion (t); VACUUM ANALYZE ccp; SELECT q, cc_q(q) FROM (VALUES ('SELECT count(*) FROM ccp WHERE n IS DISTINCT FROM 3'), ('SELECT count(*) FROM ccp WHERE a < 3 OR n <> 3'), ('SELECT count(*) FROM ccp WHERE a <> 4 OR t = ''v1'''), ('SELECT count(*) FROM ccp WHERE n <> 3 OR t = ''v1'''), ('SELECT a, count(*) FROM ccp WHERE n <> 3 OR t = ''v1'' GROUP BY a') ) v(q); q | cc_q -----------------------------------------------------------------+--------------- SELECT count(*) FROM ccp WHERE n IS DISTINCT FROM 3 | count, 1 rows SELECT count(*) FROM ccp WHERE a < 3 OR n <> 3 | count, 1 rows SELECT count(*) FROM ccp WHERE a <> 4 OR t = 'v1' | count, 1 rows SELECT count(*) FROM ccp WHERE n <> 3 OR t = 'v1' | count, 1 rows SELECT a, count(*) FROM ccp WHERE n <> 3 OR t = 'v1' GROUP BY a | count, 7 rows (5 rows) /* * A fourth complement OR is one more than the counts are doubled for, and so * a restriction no posting set answers - which every partition here implies * (a < 7): it is left out, all of it, and the clauses after it are sources of * their own, not arms of an OR it left behind. */ SELECT cc_plan('SELECT count(*) FROM ccp WHERE (n <> 1 OR t <> ''v1'') AND (n <> 2 OR t <> ''v2'') AND (n <> 3 OR t <> ''v3'') AND (a <> 100 OR n <> 4) AND n = 1 AND t = ''v4'''); cc_plan ---------------------------------------------------------------------------------------------------------------------------------------------- Custom Scan (LionCount) Partitions: ccp1, ccp2 Lion Indexes: ((n <> 1) OR (t <> 'v1'::text)), ((n <> 2) OR (t <> 'v2'::text)), ((n <> 3) OR (t <> 'v3'::text)), (n = 1), (t = 'v4'::text) Implied by Partition Bounds: ((a <> 100) OR (n <> 4)) (4 rows) SELECT q, cc_q(q) FROM (VALUES ('SELECT count(*) FROM ccp WHERE (n <> 1 OR t <> ''v1'') AND (n <> 2 OR t <> ''v2'') AND (n <> 3 OR t <> ''v3'') AND (a <> 100 OR n <> 4) AND n = 1 AND t = ''v4'''), ('SELECT a, count(*) FROM ccp WHERE (n <> 1 OR t <> ''v1'') AND (n <> 2 OR t <> ''v2'') AND (n <> 3 OR t <> ''v3'') AND (a <> 100 OR n <> 4) AND n = 1 AND t = ''v4'' GROUP BY a'), ('SELECT count(*) FROM ccp WHERE (n <> 1 OR t <> ''v1'') AND (n <> 2 OR t <> ''v2'') AND (n <> 3 OR t <> ''v3'') AND (a <> 100 OR n <> 4) AND (n = 1 OR t = ''v4'')') ) v(q); q | cc_q ------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------- SELECT count(*) FROM ccp WHERE (n <> 1 OR t <> 'v1') AND (n <> 2 OR t <> 'v2') AND (n <> 3 OR t <> 'v3') AND (a <> 100 OR n <> 4) AND n = 1 AND t = 'v4' | count, 1 rows SELECT a, count(*) FROM ccp WHERE (n <> 1 OR t <> 'v1') AND (n <> 2 OR t <> 'v2') AND (n <> 3 OR t <> 'v3') AND (a <> 100 OR n <> 4) AND n = 1 AND t = 'v4' GROUP BY a | count, 7 rows SELECT count(*) FROM ccp WHERE (n <> 1 OR t <> 'v1') AND (n <> 2 OR t <> 'v2') AND (n <> 3 OR t <> 'v3') AND (a <> 100 OR n <> 4) AND (n = 1 OR t = 'v4') | count, 1 rows (3 rows) DROP TABLE ccp; DROP FUNCTION cc_q(text); DROP FUNCTION cc_plan(text); DROP TABLE cc;