PG Test 4, PostgreSQL

PG Test 4

Language: Layout:

--PostgreSQL 9.5
--'\\' is a delimiter

drop table if exists sales_fact;

create table sales_fact(
    sales_date DATE,
    product_ID varchar(3),
    store_ID varchar(3),
    customer_ID varchar(3),
    quantity integer,
    price numeric(8,2));

/*
insert into sales_fact
VALUES (now(),1,1,1234,1,5.01),
(now(),1,1,789,2,1),
(now(),1,2,1234,1,5.02);

commit;

insert into sales_fact
select
    now(),
    round(random()*200)::text,
    round(random()*300)::text,
    round(random()*150)::text,
    round(random()*5),
    random()*5
from generate_series(1, 100000) s(i);

commit;

select 'Try full scan no index' as comment;

explain  analyze verbose select * from sales_fact where customer_id between '1234' and '1234' and product_id between '5' and '5';

CREATE INDEX ON sales_fact USING BRIN(customer_id) WITH (pages_per_range = 16);

CREATE INDEX ON sales_fact USING BRIN(sales_date) WITH (pages_per_range = 16);

CREATE INDEX ON sales_fact USING BRIN(product_id) WITH (pages_per_range = 16);

vacuum analyze sales_fact;

select 'Now try with BRIN on' as comment;

explain  analyze verbose select * from sales_fact where customer_id between '1234' and '1234' and product_id between '5' and '5';

truncate table sales_fact;

vacuum analyze sales_fact;

select 'Now try with BRIN and sorting' as comment;

explain  analyze verbose select * from sales_fact where customer_id between '1234' and '1234' and product_id between '5' and '5';

select 'Now try with a different column that isnt sorted' as comment;

explain  analyze verbose  select * from sales_fact where customer_id between '1234' and '1234' ;


absolute service time: 6,37 sec

	comment
1	Try full scan no index

	QUERY PLAN
1	Seq Scan on public.sales_fact (cost=0.00..1955.10 rows=2 width=70) (actual time=78.850..78.850 rows=0 loops=1)
2	Output: sales_date, product_id, store_id, customer_id, quantity, price
3	Filter: (((sales_fact.customer_id)::text >= '1234'::text) AND ((sales_fact.customer_id)::text <= '1234'::text) AND ((sales_fact.product_id)::text >= '5'::text) AND ((sales_fact.product_id)::text <= '5'::text))
4	Rows Removed by Filter: 100000
5	Planning time: 0.206 ms
6	Execution time: 78.904 ms

	comment
1	Now try with BRIN on

	QUERY PLAN
1	Bitmap Heap Scan on public.sales_fact (cost=12.00..16.02 rows=1 width=23) (actual time=77.451..77.451 rows=0 loops=1)
2	Output: sales_date, product_id, store_id, customer_id, quantity, price
3	Recheck Cond: (((sales_fact.product_id)::text >= '5'::text) AND ((sales_fact.product_id)::text <= '5'::text))
4	Rows Removed by Index Recheck: 99512
5	Filter: (((sales_fact.customer_id)::text >= '1234'::text) AND ((sales_fact.customer_id)::text <= '1234'::text))
6	Rows Removed by Filter: 488
7	Heap Blocks: lossy=735
8	-> Bitmap Index Scan on sales_fact_product_id_idx (cost=0.00..12.00 rows=1 width=0) (actual time=0.248..0.248 rows=7360 loops=1)
9	Index Cond: (((sales_fact.product_id)::text >= '5'::text) AND ((sales_fact.product_id)::text <= '5'::text))
10	Planning time: 0.403 ms
11	Execution time: 77.607 ms

	comment
1	Now try with BRIN and sorting

	QUERY PLAN
1	Bitmap Heap Scan on public.sales_fact (cost=12.00..16.02 rows=1 width=23) (actual time=2.872..2.872 rows=0 loops=1)
2	Output: sales_date, product_id, store_id, customer_id, quantity, price
3	Recheck Cond: (((sales_fact.product_id)::text >= '5'::text) AND ((sales_fact.product_id)::text <= '5'::text))
4	Rows Removed by Index Recheck: 1692
5	Filter: (((sales_fact.customer_id)::text >= '1234'::text) AND ((sales_fact.customer_id)::text <= '1234'::text))
6	Rows Removed by Filter: 506
7	Heap Blocks: lossy=16
8	-> Bitmap Index Scan on sales_fact_product_id_idx (cost=0.00..12.00 rows=1 width=0) (actual time=0.136..0.136 rows=160 loops=1)
9	Index Cond: (((sales_fact.product_id)::text >= '5'::text) AND ((sales_fact.product_id)::text <= '5'::text))
10	Planning time: 0.631 ms
11	Execution time: 2.955 ms

	comment
1	Now try with a different column that isnt sorted

	QUERY PLAN
1	Bitmap Heap Scan on public.sales_fact (cost=12.00..16.02 rows=1 width=23) (actual time=72.893..72.893 rows=0 loops=1)
2	Output: sales_date, product_id, store_id, customer_id, quantity, price
3	Recheck Cond: (((sales_fact.customer_id)::text >= '1234'::text) AND ((sales_fact.customer_id)::text <= '1234'::text))
4	Rows Removed by Index Recheck: 100000
5	Heap Blocks: lossy=734
6	-> Bitmap Index Scan on sales_fact_customer_id_idx (cost=0.00..12.00 rows=1 width=0) (actual time=0.242..0.242 rows=7360 loops=1)
7	Index Cond: (((sales_fact.customer_id)::text >= '1234'::text) AND ((sales_fact.customer_id)::text <= '1234'::text))
8	Planning time: 0.143 ms
9	Execution time: 72.953 ms