Horton

mcv.sql

application/sql

Download
Filename: mcv.sql
Type: application/sql
Part: 5
Message: Re: WIP: multivariate statistics / proof of concept 
DELETE FROM pg_mv_statistic;
DROP TABLE IF EXISTS test_mcv;

CREATE TABLE test_mcv (
    a INT,
    b INT,
    c INT,
    d INT,
    e INT,
    f INT,
    g INT,
    h INT,
    i INT,
    j INT,
    k INT,
    l INT,
    m INT,
    n INT,
    o INT,
    p INT,
    q INT,
    r INT,
    s INT,
    t INT,
    u INT,
    v INT,
    w INT,
    x INT,
    y INT,
    z INT
);

INSERT INTO test_mcv
     SELECT i, i, i, i, i, i, i, i, i, i, 
            i, i, i, i, i, i, i, i, i, i, 
            i, i, i, i, i, i
       FROM generate_series(1,1000000) s(i);

ALTER TABLE test_mcv ADD STATISTICS (mcv) ON (a,b);
ALTER TABLE test_mcv ADD STATISTICS (mcv) ON (b,c);
ALTER TABLE test_mcv ADD STATISTICS (mcv) ON (c,d);
ALTER TABLE test_mcv ADD STATISTICS (mcv) ON (d,e);
ALTER TABLE test_mcv ADD STATISTICS (mcv) ON (e,f);
ALTER TABLE test_mcv ADD STATISTICS (mcv) ON (f,g);
ALTER TABLE test_mcv ADD STATISTICS (mcv) ON (g,h);
ALTER TABLE test_mcv ADD STATISTICS (mcv) ON (h,i);
ALTER TABLE test_mcv ADD STATISTICS (mcv) ON (i,j);
ALTER TABLE test_mcv ADD STATISTICS (mcv) ON (j,k);
ALTER TABLE test_mcv ADD STATISTICS (mcv) ON (k,l);
ALTER TABLE test_mcv ADD STATISTICS (mcv) ON (l,m);
ALTER TABLE test_mcv ADD STATISTICS (mcv) ON (m,n);
ALTER TABLE test_mcv ADD STATISTICS (mcv) ON (n,o);
ALTER TABLE test_mcv ADD STATISTICS (mcv) ON (o,p);
ALTER TABLE test_mcv ADD STATISTICS (mcv) ON (p,q);
ALTER TABLE test_mcv ADD STATISTICS (mcv) ON (q,r);
ALTER TABLE test_mcv ADD STATISTICS (mcv) ON (r,s);
ALTER TABLE test_mcv ADD STATISTICS (mcv) ON (s,t);
ALTER TABLE test_mcv ADD STATISTICS (mcv) ON (t,u);
ALTER TABLE test_mcv ADD STATISTICS (mcv) ON (u,v);
ALTER TABLE test_mcv ADD STATISTICS (mcv) ON (v,w);
ALTER TABLE test_mcv ADD STATISTICS (mcv) ON (w,x);
ALTER TABLE test_mcv ADD STATISTICS (mcv) ON (x,y);
ALTER TABLE test_mcv ADD STATISTICS (mcv) ON (y,z);


ALTER TABLE test_mcv ADD STATISTICS (mcv) ON (a,b,c);
ALTER TABLE test_mcv ADD STATISTICS (mcv) ON (b,c,d);
ALTER TABLE test_mcv ADD STATISTICS (mcv) ON (c,d,e);
ALTER TABLE test_mcv ADD STATISTICS (mcv) ON (d,e,f);
ALTER TABLE test_mcv ADD STATISTICS (mcv) ON (e,f,g);
ALTER TABLE test_mcv ADD STATISTICS (mcv) ON (f,g,h);
ALTER TABLE test_mcv ADD STATISTICS (mcv) ON (g,h,i);
ALTER TABLE test_mcv ADD STATISTICS (mcv) ON (h,i,j);
ALTER TABLE test_mcv ADD STATISTICS (mcv) ON (i,j,k);
ALTER TABLE test_mcv ADD STATISTICS (mcv) ON (j,k,l);
ALTER TABLE test_mcv ADD STATISTICS (mcv) ON (k,l,m);
ALTER TABLE test_mcv ADD STATISTICS (mcv) ON (l,m,n);
ALTER TABLE test_mcv ADD STATISTICS (mcv) ON (m,n,o);
ALTER TABLE test_mcv ADD STATISTICS (mcv) ON (n,o,p);
ALTER TABLE test_mcv ADD STATISTICS (mcv) ON (o,p,q);
ALTER TABLE test_mcv ADD STATISTICS (mcv) ON (p,q,r);
ALTER TABLE test_mcv ADD STATISTICS (mcv) ON (q,r,s);
ALTER TABLE test_mcv ADD STATISTICS (mcv) ON (r,s,t);
ALTER TABLE test_mcv ADD STATISTICS (mcv) ON (s,t,u);
ALTER TABLE test_mcv ADD STATISTICS (mcv) ON (t,u,v);
ALTER TABLE test_mcv ADD STATISTICS (mcv) ON (u,v,w);
ALTER TABLE test_mcv ADD STATISTICS (mcv) ON (v,w,x);
ALTER TABLE test_mcv ADD STATISTICS (mcv) ON (w,x,y);
ALTER TABLE test_mcv ADD STATISTICS (mcv) ON (x,y,z);

ALTER TABLE test_mcv ADD STATISTICS (mcv) ON (a,b,c,d);
ALTER TABLE test_mcv ADD STATISTICS (mcv) ON (b,c,d,e);
ALTER TABLE test_mcv ADD STATISTICS (mcv) ON (c,d,e,f);
ALTER TABLE test_mcv ADD STATISTICS (mcv) ON (d,e,f,g);
ALTER TABLE test_mcv ADD STATISTICS (mcv) ON (e,f,g,h);
ALTER TABLE test_mcv ADD STATISTICS (mcv) ON (f,g,h,i);
ALTER TABLE test_mcv ADD STATISTICS (mcv) ON (g,h,i,j);
ALTER TABLE test_mcv ADD STATISTICS (mcv) ON (h,i,j,k);
ALTER TABLE test_mcv ADD STATISTICS (mcv) ON (i,j,k,l);
ALTER TABLE test_mcv ADD STATISTICS (mcv) ON (j,k,l,m);
ALTER TABLE test_mcv ADD STATISTICS (mcv) ON (k,l,m,n);
ALTER TABLE test_mcv ADD STATISTICS (mcv) ON (l,m,n,o);
ALTER TABLE test_mcv ADD STATISTICS (mcv) ON (m,n,o,p);
ALTER TABLE test_mcv ADD STATISTICS (mcv) ON (n,o,p,q);
ALTER TABLE test_mcv ADD STATISTICS (mcv) ON (o,p,q,r);
ALTER TABLE test_mcv ADD STATISTICS (mcv) ON (p,q,r,s);
ALTER TABLE test_mcv ADD STATISTICS (mcv) ON (q,r,s,t);
ALTER TABLE test_mcv ADD STATISTICS (mcv) ON (r,s,t,u);
ALTER TABLE test_mcv ADD STATISTICS (mcv) ON (s,t,u,v);
ALTER TABLE test_mcv ADD STATISTICS (mcv) ON (t,u,v,w);
ALTER TABLE test_mcv ADD STATISTICS (mcv) ON (u,v,w,x);
ALTER TABLE test_mcv ADD STATISTICS (mcv) ON (v,w,x,y);
ALTER TABLE test_mcv ADD STATISTICS (mcv) ON (w,x,y,z);

ALTER TABLE test_mcv ADD STATISTICS (mcv) ON (a,b,c,d,e);
ALTER TABLE test_mcv ADD STATISTICS (mcv) ON (b,c,d,e,f);
ALTER TABLE test_mcv ADD STATISTICS (mcv) ON (c,d,e,f,g);
ALTER TABLE test_mcv ADD STATISTICS (mcv) ON (d,e,f,g,h);
ALTER TABLE test_mcv ADD STATISTICS (mcv) ON (e,f,g,h,i);
ALTER TABLE test_mcv ADD STATISTICS (mcv) ON (f,g,h,i,j);
ALTER TABLE test_mcv ADD STATISTICS (mcv) ON (g,h,i,j,k);
ALTER TABLE test_mcv ADD STATISTICS (mcv) ON (h,i,j,k,l);
ALTER TABLE test_mcv ADD STATISTICS (mcv) ON (i,j,k,l,m);
ALTER TABLE test_mcv ADD STATISTICS (mcv) ON (j,k,l,m,n);
ALTER TABLE test_mcv ADD STATISTICS (mcv) ON (k,l,m,n,o);
ALTER TABLE test_mcv ADD STATISTICS (mcv) ON (l,m,n,o,p);
ALTER TABLE test_mcv ADD STATISTICS (mcv) ON (m,n,o,p,q);
ALTER TABLE test_mcv ADD STATISTICS (mcv) ON (n,o,p,q,r);
ALTER TABLE test_mcv ADD STATISTICS (mcv) ON (o,p,q,r,s);
ALTER TABLE test_mcv ADD STATISTICS (mcv) ON (p,q,r,s,t);
ALTER TABLE test_mcv ADD STATISTICS (mcv) ON (q,r,s,t,u);
ALTER TABLE test_mcv ADD STATISTICS (mcv) ON (r,s,t,u,v);
ALTER TABLE test_mcv ADD STATISTICS (mcv) ON (s,t,u,v,w);
ALTER TABLE test_mcv ADD STATISTICS (mcv) ON (t,u,v,w,x);
ALTER TABLE test_mcv ADD STATISTICS (mcv) ON (u,v,w,x,y);
ALTER TABLE test_mcv ADD STATISTICS (mcv) ON (v,w,x,y,z);

ALTER TABLE test_mcv ADD STATISTICS (mcv) ON (a,b,c,d,e,f);
ALTER TABLE test_mcv ADD STATISTICS (mcv) ON (b,c,d,e,f,g);
ALTER TABLE test_mcv ADD STATISTICS (mcv) ON (c,d,e,f,g,h);
ALTER TABLE test_mcv ADD STATISTICS (mcv) ON (d,e,f,g,h,i);
ALTER TABLE test_mcv ADD STATISTICS (mcv) ON (e,f,g,h,i,j);
ALTER TABLE test_mcv ADD STATISTICS (mcv) ON (f,g,h,i,j,k);
ALTER TABLE test_mcv ADD STATISTICS (mcv) ON (g,h,i,j,k,l);
ALTER TABLE test_mcv ADD STATISTICS (mcv) ON (h,i,j,k,l,m);
ALTER TABLE test_mcv ADD STATISTICS (mcv) ON (i,j,k,l,m,n);
ALTER TABLE test_mcv ADD STATISTICS (mcv) ON (j,k,l,m,n,o);
ALTER TABLE test_mcv ADD STATISTICS (mcv) ON (k,l,m,n,o,p);
ALTER TABLE test_mcv ADD STATISTICS (mcv) ON (l,m,n,o,p,q);
ALTER TABLE test_mcv ADD STATISTICS (mcv) ON (m,n,o,p,q,r);
ALTER TABLE test_mcv ADD STATISTICS (mcv) ON (n,o,p,q,r,s);
ALTER TABLE test_mcv ADD STATISTICS (mcv) ON (o,p,q,r,s,t);
ALTER TABLE test_mcv ADD STATISTICS (mcv) ON (p,q,r,s,t,u);
ALTER TABLE test_mcv ADD STATISTICS (mcv) ON (q,r,s,t,u,v);
ALTER TABLE test_mcv ADD STATISTICS (mcv) ON (r,s,t,u,v,w);
ALTER TABLE test_mcv ADD STATISTICS (mcv) ON (s,t,u,v,w,x);
ALTER TABLE test_mcv ADD STATISTICS (mcv) ON (t,u,v,w,x,y);
ALTER TABLE test_mcv ADD STATISTICS (mcv) ON (u,v,w,x,y,z);

ALTER TABLE test_mcv ADD STATISTICS (mcv) ON (b,c,d,e,f,g,h);
ALTER TABLE test_mcv ADD STATISTICS (mcv) ON (c,d,e,f,g,h,i);
ALTER TABLE test_mcv ADD STATISTICS (mcv) ON (d,e,f,g,h,i,j);
ALTER TABLE test_mcv ADD STATISTICS (mcv) ON (e,f,g,h,i,j,k);
ALTER TABLE test_mcv ADD STATISTICS (mcv) ON (f,g,h,i,j,k,l);
ALTER TABLE test_mcv ADD STATISTICS (mcv) ON (g,h,i,j,k,l,m);
ALTER TABLE test_mcv ADD STATISTICS (mcv) ON (h,i,j,k,l,m,n);
ALTER TABLE test_mcv ADD STATISTICS (mcv) ON (i,j,k,l,m,n,o);
ALTER TABLE test_mcv ADD STATISTICS (mcv) ON (j,k,l,m,n,o,p);
ALTER TABLE test_mcv ADD STATISTICS (mcv) ON (k,l,m,n,o,p,q);
ALTER TABLE test_mcv ADD STATISTICS (mcv) ON (l,m,n,o,p,q,r);
ALTER TABLE test_mcv ADD STATISTICS (mcv) ON (m,n,o,p,q,r,s);
ALTER TABLE test_mcv ADD STATISTICS (mcv) ON (n,o,p,q,r,s,t);
ALTER TABLE test_mcv ADD STATISTICS (mcv) ON (o,p,q,r,s,t,u);
ALTER TABLE test_mcv ADD STATISTICS (mcv) ON (p,q,r,s,t,u,v);
ALTER TABLE test_mcv ADD STATISTICS (mcv) ON (q,r,s,t,u,v,w);
ALTER TABLE test_mcv ADD STATISTICS (mcv) ON (r,s,t,u,v,w,x);
ALTER TABLE test_mcv ADD STATISTICS (mcv) ON (s,t,u,v,w,x,y);
ALTER TABLE test_mcv ADD STATISTICS (mcv) ON (t,u,v,w,x,y,z);

ALTER TABLE test_mcv ADD STATISTICS (mcv) ON (b,c,d,e,f,g,h,i);
ALTER TABLE test_mcv ADD STATISTICS (mcv) ON (c,d,e,f,g,h,i,j);
ALTER TABLE test_mcv ADD STATISTICS (mcv) ON (d,e,f,g,h,i,j,k);
ALTER TABLE test_mcv ADD STATISTICS (mcv) ON (e,f,g,h,i,j,k,l);
ALTER TABLE test_mcv ADD STATISTICS (mcv) ON (f,g,h,i,j,k,l,m);
ALTER TABLE test_mcv ADD STATISTICS (mcv) ON (g,h,i,j,k,l,m,n);
ALTER TABLE test_mcv ADD STATISTICS (mcv) ON (h,i,j,k,l,m,n,o);
ALTER TABLE test_mcv ADD STATISTICS (mcv) ON (i,j,k,l,m,n,o,p);
ALTER TABLE test_mcv ADD STATISTICS (mcv) ON (j,k,l,m,n,o,p,q);
ALTER TABLE test_mcv ADD STATISTICS (mcv) ON (k,l,m,n,o,p,q,r);
ALTER TABLE test_mcv ADD STATISTICS (mcv) ON (l,m,n,o,p,q,r,s);
ALTER TABLE test_mcv ADD STATISTICS (mcv) ON (m,n,o,p,q,r,s,t);
ALTER TABLE test_mcv ADD STATISTICS (mcv) ON (n,o,p,q,r,s,t,u);
ALTER TABLE test_mcv ADD STATISTICS (mcv) ON (o,p,q,r,s,t,u,v);
ALTER TABLE test_mcv ADD STATISTICS (mcv) ON (p,q,r,s,t,u,v,w);
ALTER TABLE test_mcv ADD STATISTICS (mcv) ON (q,r,s,t,u,v,w,x);
ALTER TABLE test_mcv ADD STATISTICS (mcv) ON (r,s,t,u,v,w,x,y);
ALTER TABLE test_mcv ADD STATISTICS (mcv) ON (s,t,u,v,w,x,y,z);

ANALYZE test_mcv;

-- should be 0
SELECT COUNT(*) FROM pg_mv_stats WHERE depsbytes IS NOT NULL;

TRUNCATE test_mcv;

INSERT INTO test_mcv
     SELECT v, v, v, v, v, v, v, v, v, v, 
            v, v, v, v, v, v, v, v, v, v, 
            v, v, v, v, v, v
       FROM (
           SELECT mod(i,1000) AS v FROM generate_series(1,1000000) s(i)
       ) foo;

ANALYZE test_mcv;

-- should be 151 (everything implies everything)
SELECT COUNT(*) FROM pg_mv_stats WHERE depsbytes IS NOT NULL;

EXPLAIN SELECT * FROM test_mcv WHERE (a=1);
EXPLAIN SELECT * FROM test_mcv WHERE (a=1) AND (b=1);
EXPLAIN SELECT * FROM test_mcv WHERE (a=1) AND (b=1) AND (c=1);
EXPLAIN SELECT * FROM test_mcv WHERE (a=1) AND (b=1) AND (c=1) AND (d=1);
EXPLAIN SELECT * FROM test_mcv WHERE (a=1) AND (b=1) AND (c=1) AND (d=1) AND (e=1);
EXPLAIN SELECT * FROM test_mcv WHERE (a=1) AND (b=1) AND (c=1) AND (d=1) AND (e=1) AND (f=1);
EXPLAIN SELECT * FROM test_mcv WHERE (a=1) AND (b=1) AND (c=1) AND (d=1) AND (e=1) AND (f=1) AND (g=1);
EXPLAIN SELECT * FROM test_mcv WHERE (a=1) AND (b=1) AND (c=1) AND (d=1) AND (e=1) AND (f=1) AND (g=1) AND (h=1);
EXPLAIN SELECT * FROM test_mcv WHERE (a=1) AND (b=1) AND (c=1) AND (d=1) AND (e=1) AND (f=1) AND (g=1) AND (h=1) AND (i=1);
EXPLAIN SELECT * FROM test_mcv WHERE (a=1) AND (b=1) AND (c=1) AND (d=1) AND (e=1) AND (f=1) AND (g=1) AND (h=1) AND (i=1) AND (j=1);
EXPLAIN SELECT * FROM test_mcv WHERE (a=1) AND (b=1) AND (c=1) AND (d=1) AND (e=1) AND (f=1) AND (g=1) AND (h=1) AND (i=1) AND (j=1) AND (k=1);
EXPLAIN SELECT * FROM test_mcv WHERE (a=1) AND (b=1) AND (c=1) AND (d=1) AND (e=1) AND (f=1) AND (g=1) AND (h=1) AND (i=1) AND (j=1) AND (k=1) AND (l=1);
EXPLAIN SELECT * FROM test_mcv WHERE (a=1) AND (b=1) AND (c=1) AND (d=1) AND (e=1) AND (f=1) AND (g=1) AND (h=1) AND (i=1) AND (j=1) AND (k=1) AND (l=1) AND (m=1);
EXPLAIN SELECT * FROM test_mcv WHERE (a=1) AND (b=1) AND (c=1) AND (d=1) AND (e=1) AND (f=1) AND (g=1) AND (h=1) AND (i=1) AND (j=1) AND (k=1) AND (l=1) AND (m=1) AND (n=1);
EXPLAIN SELECT * FROM test_mcv WHERE (a=1) AND (b=1) AND (c=1) AND (d=1) AND (e=1) AND (f=1) AND (g=1) AND (h=1) AND (i=1) AND (j=1) AND (k=1) AND (l=1) AND (m=1) AND (n=1) AND (o=1);
EXPLAIN SELECT * FROM test_mcv WHERE (a=1) AND (b=1) AND (c=1) AND (d=1) AND (e=1) AND (f=1) AND (g=1) AND (h=1) AND (i=1) AND (j=1) AND (k=1) AND (l=1) AND (m=1) AND (n=1) AND (o=1) AND (p=1);
EXPLAIN SELECT * FROM test_mcv WHERE (a=1) AND (b=1) AND (c=1) AND (d=1) AND (e=1) AND (f=1) AND (g=1) AND (h=1) AND (i=1) AND (j=1) AND (k=1) AND (l=1) AND (m=1) AND (n=1) AND (o=1) AND (p=1) AND (q=1);
EXPLAIN SELECT * FROM test_mcv WHERE (a=1) AND (b=1) AND (c=1) AND (d=1) AND (e=1) AND (f=1) AND (g=1) AND (h=1) AND (i=1) AND (j=1) AND (k=1) AND (l=1) AND (m=1) AND (n=1) AND (o=1) AND (p=1) AND (q=1) AND (r=1);
EXPLAIN SELECT * FROM test_mcv WHERE (a=1) AND (b=1) AND (c=1) AND (d=1) AND (e=1) AND (f=1) AND (g=1) AND (h=1) AND (i=1) AND (j=1) AND (k=1) AND (l=1) AND (m=1) AND (n=1) AND (o=1) AND (p=1) AND (q=1) AND (r=1) AND (s=1);
EXPLAIN SELECT * FROM test_mcv WHERE (a=1) AND (b=1) AND (c=1) AND (d=1) AND (e=1) AND (f=1) AND (g=1) AND (h=1) AND (i=1) AND (j=1) AND (k=1) AND (l=1) AND (m=1) AND (n=1) AND (o=1) AND (p=1) AND (q=1) AND (r=1) AND (s=1) AND (t=1);
EXPLAIN SELECT * FROM test_mcv WHERE (a=1) AND (b=1) AND (c=1) AND (d=1) AND (e=1) AND (f=1) AND (g=1) AND (h=1) AND (i=1) AND (j=1) AND (k=1) AND (l=1) AND (m=1) AND (n=1) AND (o=1) AND (p=1) AND (q=1) AND (r=1) AND (s=1) AND (t=1) AND (u=1);
EXPLAIN SELECT * FROM test_mcv WHERE (a=1) AND (b=1) AND (c=1) AND (d=1) AND (e=1) AND (f=1) AND (g=1) AND (h=1) AND (i=1) AND (j=1) AND (k=1) AND (l=1) AND (m=1) AND (n=1) AND (o=1) AND (p=1) AND (q=1) AND (r=1) AND (s=1) AND (t=1) AND (u=1) AND (v=1);
EXPLAIN SELECT * FROM test_mcv WHERE (a=1) AND (b=1) AND (c=1) AND (d=1) AND (e=1) AND (f=1) AND (g=1) AND (h=1) AND (i=1) AND (j=1) AND (k=1) AND (l=1) AND (m=1) AND (n=1) AND (o=1) AND (p=1) AND (q=1) AND (r=1) AND (s=1) AND (t=1) AND (u=1) AND (v=1) AND (w=1);
EXPLAIN SELECT * FROM test_mcv WHERE (a=1) AND (b=1) AND (c=1) AND (d=1) AND (e=1) AND (f=1) AND (g=1) AND (h=1) AND (i=1) AND (j=1) AND (k=1) AND (l=1) AND (m=1) AND (n=1) AND (o=1) AND (p=1) AND (q=1) AND (r=1) AND (s=1) AND (t=1) AND (u=1) AND (v=1) AND (w=1) AND (x=1);
EXPLAIN SELECT * FROM test_mcv WHERE (a=1) AND (b=1) AND (c=1) AND (d=1) AND (e=1) AND (f=1) AND (g=1) AND (h=1) AND (i=1) AND (j=1) AND (k=1) AND (l=1) AND (m=1) AND (n=1) AND (o=1) AND (p=1) AND (q=1) AND (r=1) AND (s=1) AND (t=1) AND (u=1) AND (v=1) AND (w=1) AND (x=1) AND (y=1);
EXPLAIN SELECT * FROM test_mcv WHERE (a=1) AND (b=1) AND (c=1) AND (d=1) AND (e=1) AND (f=1) AND (g=1) AND (h=1) AND (i=1) AND (j=1) AND (k=1) AND (l=1) AND (m=1) AND (n=1) AND (o=1) AND (p=1) AND (q=1) AND (r=1) AND (s=1) AND (t=1) AND (u=1) AND (v=1) AND (w=1) AND (x=1) AND (y=1) AND (z=1);

EXPLAIN SELECT * FROM test_mcv WHERE (a=1);
EXPLAIN SELECT * FROM test_mcv WHERE (a=1) AND (b=1);
EXPLAIN SELECT * FROM test_mcv WHERE (a=1) AND (b=1) AND (c=1);
EXPLAIN SELECT * FROM test_mcv WHERE (a=1) AND (b=1) AND (c=1) AND (d=1);
EXPLAIN SELECT * FROM test_mcv WHERE (a=1) AND (b=1) AND (c=1) AND (e=1);
EXPLAIN SELECT * FROM test_mcv WHERE (a=1) AND (b=1) AND (c=1) AND (e=1) AND (f=1);
EXPLAIN SELECT * FROM test_mcv WHERE (a=1) AND (b=1) AND (c=1) AND (e=1) AND (f=1) AND (g=1);
EXPLAIN SELECT * FROM test_mcv WHERE (a=1) AND (b=1) AND (c=1) AND (e=1) AND (f=1) AND (g=1) AND (h=1);
EXPLAIN SELECT * FROM test_mcv WHERE (a=1) AND (b=1) AND (c=1) AND (e=1) AND (f=1) AND (g=1) AND (i=1);
EXPLAIN SELECT * FROM test_mcv WHERE (a=1) AND (b=1) AND (c=1) AND (e=1) AND (f=1) AND (g=1) AND (i=1) AND (j=1);
EXPLAIN SELECT * FROM test_mcv WHERE (a=1) AND (b=1) AND (c=1) AND (e=1) AND (f=1) AND (g=1) AND (i=1) AND (j=1) AND (k=1);
EXPLAIN SELECT * FROM test_mcv WHERE (a=1) AND (b=1) AND (c=1) AND (e=1) AND (f=1) AND (g=1) AND (i=1) AND (j=1) AND (k=1) AND (l=1);
EXPLAIN SELECT * FROM test_mcv WHERE (a=1) AND (b=1) AND (c=1) AND (e=1) AND (f=1) AND (g=1) AND (i=1) AND (j=1) AND (k=1) AND (m=1);
EXPLAIN SELECT * FROM test_mcv WHERE (a=1) AND (b=1) AND (c=1) AND (e=1) AND (f=1) AND (g=1) AND (i=1) AND (j=1) AND (k=1) AND (m=1) AND (n=1);
EXPLAIN SELECT * FROM test_mcv WHERE (a=1) AND (b=1) AND (c=1) AND (e=1) AND (f=1) AND (g=1) AND (i=1) AND (j=1) AND (k=1) AND (m=1) AND (n=1) AND (o=1);
EXPLAIN SELECT * FROM test_mcv WHERE (a=1) AND (b=1) AND (c=1) AND (e=1) AND (f=1) AND (g=1) AND (i=1) AND (j=1) AND (k=1) AND (m=1) AND (n=1) AND (o=1) AND (p=1);
EXPLAIN SELECT * FROM test_mcv WHERE (a=1) AND (b=1) AND (c=1) AND (e=1) AND (f=1) AND (g=1) AND (i=1) AND (j=1) AND (k=1) AND (m=1) AND (n=1) AND (o=1) AND (p=1) AND (q=1);
EXPLAIN SELECT * FROM test_mcv WHERE (a=1) AND (b=1) AND (c=1) AND (e=1) AND (f=1) AND (g=1) AND (i=1) AND (j=1) AND (k=1) AND (m=1) AND (n=1) AND (o=1) AND (q=1) AND (r=1);
EXPLAIN SELECT * FROM test_mcv WHERE (a=1) AND (b=1) AND (c=1) AND (e=1) AND (f=1) AND (g=1) AND (i=1) AND (j=1) AND (k=1) AND (m=1) AND (n=1) AND (o=1) AND (q=1) AND (r=1) AND (s=1);
EXPLAIN SELECT * FROM test_mcv WHERE (a=1) AND (b=1) AND (c=1) AND (e=1) AND (f=1) AND (g=1) AND (i=1) AND (j=1) AND (k=1) AND (m=1) AND (n=1) AND (o=1) AND (q=1) AND (r=1) AND (s=1) AND (t=1);
EXPLAIN SELECT * FROM test_mcv WHERE (a=1) AND (b=1) AND (c=1) AND (e=1) AND (f=1) AND (g=1) AND (i=1) AND (j=1) AND (k=1) AND (m=1) AND (n=1) AND (o=1) AND (q=1) AND (r=1) AND (s=1) AND (t=1) AND (u=1);
EXPLAIN SELECT * FROM test_mcv WHERE (a=1) AND (b=1) AND (c=1) AND (e=1) AND (f=1) AND (g=1) AND (i=1) AND (j=1) AND (k=1) AND (m=1) AND (n=1) AND (o=1) AND (q=1) AND (r=1) AND (s=1) AND (t=1) AND (u=1) AND (v=1);
EXPLAIN SELECT * FROM test_mcv WHERE (a=1) AND (b=1) AND (c=1) AND (e=1) AND (f=1) AND (g=1) AND (i=1) AND (j=1) AND (k=1) AND (m=1) AND (n=1) AND (o=1) AND (q=1) AND (r=1) AND (s=1) AND (t=1) AND (u=1) AND (w=1);
EXPLAIN SELECT * FROM test_mcv WHERE (a=1) AND (b=1) AND (c=1) AND (e=1) AND (f=1) AND (g=1) AND (i=1) AND (j=1) AND (k=1) AND (m=1) AND (n=1) AND (o=1) AND (q=1) AND (r=1) AND (s=1) AND (t=1) AND (u=1) AND (w=1) AND (x=1);
EXPLAIN SELECT * FROM test_mcv WHERE (a=1) AND (b=1) AND (c=1) AND (e=1) AND (f=1) AND (g=1) AND (i=1) AND (j=1) AND (k=1) AND (m=1) AND (n=1) AND (o=1) AND (q=1) AND (r=1) AND (s=1) AND (t=1) AND (u=1) AND (w=1) AND (x=1) AND (y=1);
EXPLAIN SELECT * FROM test_mcv WHERE (a=1) AND (b=1) AND (c=1) AND (e=1) AND (f=1) AND (g=1) AND (i=1) AND (j=1) AND (k=1) AND (m=1) AND (n=1) AND (o=1) AND (q=1) AND (r=1) AND (s=1) AND (t=1) AND (u=1) AND (w=1) AND (x=1) AND (y=1) AND (z=1);

TRUNCATE test_mcv;

INSERT INTO test_mcv
     SELECT v, v, v, v, v, v, v, v, v, v, 
            v, v, v, v, v, v, v, v, v, v, 
            v, v, v, v, v, v
       FROM (
           SELECT (mod(i,1000) * random() * random())::int AS v FROM generate_series(1,1000000) s(i)
       ) foo;

ANALYZE test_mcv;

-- should be 151 (everything implies everything)
SELECT COUNT(*) FROM pg_mv_stats WHERE depsbytes IS NOT NULL;

EXPLAIN SELECT * FROM test_mcv WHERE (a=1);
EXPLAIN SELECT * FROM test_mcv WHERE (a=1) AND (b=1);
EXPLAIN SELECT * FROM test_mcv WHERE (a=1) AND (b=1) AND (c=1);
EXPLAIN SELECT * FROM test_mcv WHERE (a=1) AND (b=1) AND (c=1) AND (d=1);
EXPLAIN SELECT * FROM test_mcv WHERE (a=1) AND (b=1) AND (c=1) AND (d=1) AND (e=1);
EXPLAIN SELECT * FROM test_mcv WHERE (a=1) AND (b=1) AND (c=1) AND (d=1) AND (e=1) AND (f=1);
EXPLAIN SELECT * FROM test_mcv WHERE (a=1) AND (b=1) AND (c=1) AND (d=1) AND (e=1) AND (f=1) AND (g=1);
EXPLAIN SELECT * FROM test_mcv WHERE (a=1) AND (b=1) AND (c=1) AND (d=1) AND (e=1) AND (f=1) AND (g=1) AND (h=1);
EXPLAIN SELECT * FROM test_mcv WHERE (a=1) AND (b=1) AND (c=1) AND (d=1) AND (e=1) AND (f=1) AND (g=1) AND (h=1) AND (i=1);
EXPLAIN SELECT * FROM test_mcv WHERE (a=1) AND (b=1) AND (c=1) AND (d=1) AND (e=1) AND (f=1) AND (g=1) AND (h=1) AND (i=1) AND (j=1);
EXPLAIN SELECT * FROM test_mcv WHERE (a=1) AND (b=1) AND (c=1) AND (d=1) AND (e=1) AND (f=1) AND (g=1) AND (h=1) AND (i=1) AND (j=1) AND (k=1);
EXPLAIN SELECT * FROM test_mcv WHERE (a=1) AND (b=1) AND (c=1) AND (d=1) AND (e=1) AND (f=1) AND (g=1) AND (h=1) AND (i=1) AND (j=1) AND (k=1) AND (l=1);
EXPLAIN SELECT * FROM test_mcv WHERE (a=1) AND (b=1) AND (c=1) AND (d=1) AND (e=1) AND (f=1) AND (g=1) AND (h=1) AND (i=1) AND (j=1) AND (k=1) AND (l=1) AND (m=1);
EXPLAIN SELECT * FROM test_mcv WHERE (a=1) AND (b=1) AND (c=1) AND (d=1) AND (e=1) AND (f=1) AND (g=1) AND (h=1) AND (i=1) AND (j=1) AND (k=1) AND (l=1) AND (m=1) AND (n=1);
EXPLAIN SELECT * FROM test_mcv WHERE (a=1) AND (b=1) AND (c=1) AND (d=1) AND (e=1) AND (f=1) AND (g=1) AND (h=1) AND (i=1) AND (j=1) AND (k=1) AND (l=1) AND (m=1) AND (n=1) AND (o=1);
EXPLAIN SELECT * FROM test_mcv WHERE (a=1) AND (b=1) AND (c=1) AND (d=1) AND (e=1) AND (f=1) AND (g=1) AND (h=1) AND (i=1) AND (j=1) AND (k=1) AND (l=1) AND (m=1) AND (n=1) AND (o=1) AND (p=1);
EXPLAIN SELECT * FROM test_mcv WHERE (a=1) AND (b=1) AND (c=1) AND (d=1) AND (e=1) AND (f=1) AND (g=1) AND (h=1) AND (i=1) AND (j=1) AND (k=1) AND (l=1) AND (m=1) AND (n=1) AND (o=1) AND (p=1) AND (q=1);
EXPLAIN SELECT * FROM test_mcv WHERE (a=1) AND (b=1) AND (c=1) AND (d=1) AND (e=1) AND (f=1) AND (g=1) AND (h=1) AND (i=1) AND (j=1) AND (k=1) AND (l=1) AND (m=1) AND (n=1) AND (o=1) AND (p=1) AND (q=1) AND (r=1);
EXPLAIN SELECT * FROM test_mcv WHERE (a=1) AND (b=1) AND (c=1) AND (d=1) AND (e=1) AND (f=1) AND (g=1) AND (h=1) AND (i=1) AND (j=1) AND (k=1) AND (l=1) AND (m=1) AND (n=1) AND (o=1) AND (p=1) AND (q=1) AND (r=1) AND (s=1);
EXPLAIN SELECT * FROM test_mcv WHERE (a=1) AND (b=1) AND (c=1) AND (d=1) AND (e=1) AND (f=1) AND (g=1) AND (h=1) AND (i=1) AND (j=1) AND (k=1) AND (l=1) AND (m=1) AND (n=1) AND (o=1) AND (p=1) AND (q=1) AND (r=1) AND (s=1) AND (t=1);
EXPLAIN SELECT * FROM test_mcv WHERE (a=1) AND (b=1) AND (c=1) AND (d=1) AND (e=1) AND (f=1) AND (g=1) AND (h=1) AND (i=1) AND (j=1) AND (k=1) AND (l=1) AND (m=1) AND (n=1) AND (o=1) AND (p=1) AND (q=1) AND (r=1) AND (s=1) AND (t=1) AND (u=1);
EXPLAIN SELECT * FROM test_mcv WHERE (a=1) AND (b=1) AND (c=1) AND (d=1) AND (e=1) AND (f=1) AND (g=1) AND (h=1) AND (i=1) AND (j=1) AND (k=1) AND (l=1) AND (m=1) AND (n=1) AND (o=1) AND (p=1) AND (q=1) AND (r=1) AND (s=1) AND (t=1) AND (u=1) AND (v=1);
EXPLAIN SELECT * FROM test_mcv WHERE (a=1) AND (b=1) AND (c=1) AND (d=1) AND (e=1) AND (f=1) AND (g=1) AND (h=1) AND (i=1) AND (j=1) AND (k=1) AND (l=1) AND (m=1) AND (n=1) AND (o=1) AND (p=1) AND (q=1) AND (r=1) AND (s=1) AND (t=1) AND (u=1) AND (v=1) AND (w=1);
EXPLAIN SELECT * FROM test_mcv WHERE (a=1) AND (b=1) AND (c=1) AND (d=1) AND (e=1) AND (f=1) AND (g=1) AND (h=1) AND (i=1) AND (j=1) AND (k=1) AND (l=1) AND (m=1) AND (n=1) AND (o=1) AND (p=1) AND (q=1) AND (r=1) AND (s=1) AND (t=1) AND (u=1) AND (v=1) AND (w=1) AND (x=1);
EXPLAIN SELECT * FROM test_mcv WHERE (a=1) AND (b=1) AND (c=1) AND (d=1) AND (e=1) AND (f=1) AND (g=1) AND (h=1) AND (i=1) AND (j=1) AND (k=1) AND (l=1) AND (m=1) AND (n=1) AND (o=1) AND (p=1) AND (q=1) AND (r=1) AND (s=1) AND (t=1) AND (u=1) AND (v=1) AND (w=1) AND (x=1) AND (y=1);
EXPLAIN SELECT * FROM test_mcv WHERE (a=1) AND (b=1) AND (c=1) AND (d=1) AND (e=1) AND (f=1) AND (g=1) AND (h=1) AND (i=1) AND (j=1) AND (k=1) AND (l=1) AND (m=1) AND (n=1) AND (o=1) AND (p=1) AND (q=1) AND (r=1) AND (s=1) AND (t=1) AND (u=1) AND (v=1) AND (w=1) AND (x=1) AND (y=1) AND (z=1);

EXPLAIN SELECT * FROM test_mcv WHERE (a=1);
EXPLAIN SELECT * FROM test_mcv WHERE (a=1) AND (b=1);
EXPLAIN SELECT * FROM test_mcv WHERE (a=1) AND (b=1) AND (c=1);
EXPLAIN SELECT * FROM test_mcv WHERE (a=1) AND (b=1) AND (c=1) AND (d=1);
EXPLAIN SELECT * FROM test_mcv WHERE (a=1) AND (b=1) AND (c=1) AND (e=1);
EXPLAIN SELECT * FROM test_mcv WHERE (a=1) AND (b=1) AND (c=1) AND (e=1) AND (f=1);
EXPLAIN SELECT * FROM test_mcv WHERE (a=1) AND (b=1) AND (c=1) AND (e=1) AND (f=1) AND (g=1);
EXPLAIN SELECT * FROM test_mcv WHERE (a=1) AND (b=1) AND (c=1) AND (e=1) AND (f=1) AND (g=1) AND (h=1);
EXPLAIN SELECT * FROM test_mcv WHERE (a=1) AND (b=1) AND (c=1) AND (e=1) AND (f=1) AND (g=1) AND (i=1);
EXPLAIN SELECT * FROM test_mcv WHERE (a=1) AND (b=1) AND (c=1) AND (e=1) AND (f=1) AND (g=1) AND (i=1) AND (j=1);
EXPLAIN SELECT * FROM test_mcv WHERE (a=1) AND (b=1) AND (c=1) AND (e=1) AND (f=1) AND (g=1) AND (i=1) AND (j=1) AND (k=1);
EXPLAIN SELECT * FROM test_mcv WHERE (a=1) AND (b=1) AND (c=1) AND (e=1) AND (f=1) AND (g=1) AND (i=1) AND (j=1) AND (k=1) AND (l=1);
EXPLAIN SELECT * FROM test_mcv WHERE (a=1) AND (b=1) AND (c=1) AND (e=1) AND (f=1) AND (g=1) AND (i=1) AND (j=1) AND (k=1) AND (m=1);
EXPLAIN SELECT * FROM test_mcv WHERE (a=1) AND (b=1) AND (c=1) AND (e=1) AND (f=1) AND (g=1) AND (i=1) AND (j=1) AND (k=1) AND (m=1) AND (n=1);
EXPLAIN SELECT * FROM test_mcv WHERE (a=1) AND (b=1) AND (c=1) AND (e=1) AND (f=1) AND (g=1) AND (i=1) AND (j=1) AND (k=1) AND (m=1) AND (n=1) AND (o=1);
EXPLAIN SELECT * FROM test_mcv WHERE (a=1) AND (b=1) AND (c=1) AND (e=1) AND (f=1) AND (g=1) AND (i=1) AND (j=1) AND (k=1) AND (m=1) AND (n=1) AND (o=1) AND (p=1);
EXPLAIN SELECT * FROM test_mcv WHERE (a=1) AND (b=1) AND (c=1) AND (e=1) AND (f=1) AND (g=1) AND (i=1) AND (j=1) AND (k=1) AND (m=1) AND (n=1) AND (o=1) AND (p=1) AND (q=1);
EXPLAIN SELECT * FROM test_mcv WHERE (a=1) AND (b=1) AND (c=1) AND (e=1) AND (f=1) AND (g=1) AND (i=1) AND (j=1) AND (k=1) AND (m=1) AND (n=1) AND (o=1) AND (q=1) AND (r=1);
EXPLAIN SELECT * FROM test_mcv WHERE (a=1) AND (b=1) AND (c=1) AND (e=1) AND (f=1) AND (g=1) AND (i=1) AND (j=1) AND (k=1) AND (m=1) AND (n=1) AND (o=1) AND (q=1) AND (r=1) AND (s=1);
EXPLAIN SELECT * FROM test_mcv WHERE (a=1) AND (b=1) AND (c=1) AND (e=1) AND (f=1) AND (g=1) AND (i=1) AND (j=1) AND (k=1) AND (m=1) AND (n=1) AND (o=1) AND (q=1) AND (r=1) AND (s=1) AND (t=1);
EXPLAIN SELECT * FROM test_mcv WHERE (a=1) AND (b=1) AND (c=1) AND (e=1) AND (f=1) AND (g=1) AND (i=1) AND (j=1) AND (k=1) AND (m=1) AND (n=1) AND (o=1) AND (q=1) AND (r=1) AND (s=1) AND (t=1) AND (u=1);
EXPLAIN SELECT * FROM test_mcv WHERE (a=1) AND (b=1) AND (c=1) AND (e=1) AND (f=1) AND (g=1) AND (i=1) AND (j=1) AND (k=1) AND (m=1) AND (n=1) AND (o=1) AND (q=1) AND (r=1) AND (s=1) AND (t=1) AND (u=1) AND (v=1);
EXPLAIN SELECT * FROM test_mcv WHERE (a=1) AND (b=1) AND (c=1) AND (e=1) AND (f=1) AND (g=1) AND (i=1) AND (j=1) AND (k=1) AND (m=1) AND (n=1) AND (o=1) AND (q=1) AND (r=1) AND (s=1) AND (t=1) AND (u=1) AND (w=1);
EXPLAIN SELECT * FROM test_mcv WHERE (a=1) AND (b=1) AND (c=1) AND (e=1) AND (f=1) AND (g=1) AND (i=1) AND (j=1) AND (k=1) AND (m=1) AND (n=1) AND (o=1) AND (q=1) AND (r=1) AND (s=1) AND (t=1) AND (u=1) AND (w=1) AND (x=1);
EXPLAIN SELECT * FROM test_mcv WHERE (a=1) AND (b=1) AND (c=1) AND (e=1) AND (f=1) AND (g=1) AND (i=1) AND (j=1) AND (k=1) AND (m=1) AND (n=1) AND (o=1) AND (q=1) AND (r=1) AND (s=1) AND (t=1) AND (u=1) AND (w=1) AND (x=1) AND (y=1);
EXPLAIN SELECT * FROM test_mcv WHERE (a=1) AND (b=1) AND (c=1) AND (e=1) AND (f=1) AND (g=1) AND (i=1) AND (j=1) AND (k=1) AND (m=1) AND (n=1) AND (o=1) AND (q=1) AND (r=1) AND (s=1) AND (t=1) AND (u=1) AND (w=1) AND (x=1) AND (y=1) AND (z=1);