Skip to content

Instantly share code, notes, and snippets.

View piotrwlodarczyk's full-sized avatar

piotrwlodarczyk

View GitHub Profile
@piotrwlodarczyk
piotrwlodarczyk / pruning on
Created December 23, 2019 15:19
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------
Append (cost=0.00..2666.72 rows=587 width=52) (actual time=0.011..0.227 rows=1 loops=1)
-> Seq Scan on "public.book_1-1000_1990-01-01" (cost=0.00..1.01 rows=1 width=52) (actual time=0.009..0.010 rows=1 loops=1)
Filter: (id_owner = 4)
-> Seq Scan on "public.book_1-1000_1990-04-01" (cost=0.00..22.75 rows=5 width=52) (actual time=0.003..0.003 rows=0 loops=1)
Filter: (id_owner = 4)
-> Seq Scan on "public.book_1-1000_1990-07-01" (cost=0.00..22.75 rows=5 width=52) (actual time=0.001..0.001 rows=0 loops=1)
Filter: (id_owner = 4)
-> Seq Scan on "public.book_1-1000_1990-10-01" (cost=0.00..22.75 rows=5 width=52) (actual time=0.001..0.001 rows=0 loops=1)
@piotrwlodarczyk
piotrwlodarczyk / pruning off
Created December 23, 2019 15:18
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------
Gather (cost=1000.00..22398.11 rows=5942 width=52) (actual time=22.004..87.457 rows=1 loops=1)
Workers Planned: 2
Workers Launched: 2
-> Parallel Append (cost=0.00..20803.91 rows=2378 width=52) (actual time=0.005..0.267 rows=0 loops=3)
-> Parallel Seq Scan on "public.book_1-1000_1990-04-01" (cost=0.00..17.50 rows=3 width=52) (actual time=0.000..0.000 rows=0 loops=1)
Filter: (id_owner = 4)
-> Parallel Seq Scan on "public.book_1-1000_1990-07-01" (cost=0.00..17.50 rows=3 width=52) (actual time=0.000..0.000 rows=0 loops=1)
Filter: (id_owner = 4)
@piotrwlodarczyk
piotrwlodarczyk / update on partition key
Created December 23, 2019 12:47
QUERY PLAN
---------------------------------------------------------------------------------------
Update on book (cost=0.00..2663.79 rows=587 width=58)
Update on "public.book_1-1000_1990-01-01"
Update on "public.book_1-1000_1990-04-01"
Update on "public.book_1-1000_1990-07-01"
Update on "public.book_1-1000_1990-10-01"
Update on "public.book_1-1000_1991-01-01"
Update on "public.book_1-1000_1991-04-01"
Update on "public.book_1-1000_1991-07-01"
@piotrwlodarczyk
piotrwlodarczyk / update on index
Created December 23, 2019 12:47
QUERY PLAN
---------------------------------------------------------------------------------------------------------------
Update on book (cost=0.00..15044.24 rows=5942 width=58)
Update on "public.book_1-1000_1990-01-01"
Update on "public.book_1-1000_1990-04-01"
Update on "public.book_1-1000_1990-07-01"
Update on "public.book_1-1000_1990-10-01"
Update on "public.book_1-1000_1991-01-01"
Update on "public.book_1-1000_1991-04-01"
Update on "public.book_1-1000_1991-07-01"
@piotrwlodarczyk
piotrwlodarczyk / Database structure
Created December 23, 2019 12:43
mytest=# \d+ book;
Partitioned table "public.book"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
----------+--------+-----------+----------+----------------------------------+----------+--------------+-------------
id | bigint | | not null | nextval('book_id_seq'::regclass) | plain | |
id_owner | bigint | | not null | | plain | |
added | date | | not null | | plain | |
isbn | text | | | | extended | |
Partition key: RANGE (id_owner)
Indexes:
@piotrwlodarczyk
piotrwlodarczyk / 6000 explain on partitioned
Created August 12, 2019 12:29
"Append (cost=0.00..3723.51 rows=952 width=20) (actual time=0.859..0.859 rows=0 loops=1)"
" -> Seq Scan on "public.book_1-1000_1990-01-01" (cost=0.00..31.25 rows=8 width=20) (actual time=0.011..0.011 rows=0 loops=1)"
" Filter: (id_owner = 4)"
" -> Seq Scan on "public.book_1-1000_1990-04-01" (cost=0.00..31.25 rows=8 width=20) (actual time=0.006..0.007 rows=0 loops=1)"
" Filter: (id_owner = 4)"
" -> Seq Scan on "public.book_1-1000_1990-07-01" (cost=0.00..31.25 rows=8 width=20) (actual time=0.004..0.004 rows=0 loops=1)"
" Filter: (id_owner = 4)"
" -> Seq Scan on "public.book_1-1000_1990-10-01" (cost=0.00..31.25 rows=8 width=20) (actual time=0.004..0.004 rows=0 loops=1)"
" Filter: (id_owner = 4)"
" -> Seq Scan on "public.book_1-1000_1991-01-01" (cost=0.00..31.25 rows=8 width=20) (actual time=0.004..0.004 rows=0 loops=1)"
@piotrwlodarczyk
piotrwlodarczyk / 12000 explain on partitioned
Created August 12, 2019 12:26
"Append (cost=0.00..3723.51 rows=952 width=20) (actual time=0.477..0.477 rows=0 loops=1)"
" -> Seq Scan on "public.book_1-1000_1990-01-01" (cost=0.00..31.25 rows=8 width=20) (actual time=0.008..0.008 rows=0 loops=1)"
" Filter: (id_owner = 4)"
" -> Seq Scan on "public.book_1-1000_1990-04-01" (cost=0.00..31.25 rows=8 width=20) (actual time=0.005..0.005 rows=0 loops=1)"
" Filter: (id_owner = 4)"
" -> Seq Scan on "public.book_1-1000_1990-07-01" (cost=0.00..31.25 rows=8 width=20) (actual time=0.004..0.004 rows=0 loops=1)"
" Filter: (id_owner = 4)"
" -> Seq Scan on "public.book_1-1000_1990-10-01" (cost=0.00..31.25 rows=8 width=20) (actual time=0.003..0.004 rows=0 loops=1)"
" Filter: (id_owner = 4)"
" -> Seq Scan on "public.book_1-1000_1991-01-01" (cost=0.00..31.25 rows=8 width=20) (actual time=0.003..0.003 rows=0 loops=1)"
@piotrwlodarczyk
piotrwlodarczyk / 6000 explain
Created August 12, 2019 12:15
"Append (cost=0.00..3723.51 rows=952 width=20) (actual time=49.597..49.597 rows=0 loops=1)"
" -> Seq Scan on "public.book_1-1000_1990-01-01" (cost=0.00..31.25 rows=8 width=20) (actual time=0.908..0.908 rows=0 loops=1)"
" Filter: (id_owner = 4)"
" -> Seq Scan on "public.book_1-1000_1990-04-01" (cost=0.00..31.25 rows=8 width=20) (actual time=0.479..0.479 rows=0 loops=1)"
" Filter: (id_owner = 4)"
" -> Seq Scan on "public.book_1-1000_1990-07-01" (cost=0.00..31.25 rows=8 width=20) (actual time=0.482..0.482 rows=0 loops=1)"
" Filter: (id_owner = 4)"
" -> Seq Scan on "public.book_1-1000_1990-10-01" (cost=0.00..31.25 rows=8 width=20) (actual time=0.517..0.518 rows=0 loops=1)"
" Filter: (id_owner = 4)"
" -> Seq Scan on "public.book_1-1000_1991-01-01" (cost=0.00..31.25 rows=8 width=20) (actual time=0.507..0.507 rows=0 loops=1)"
@piotrwlodarczyk
piotrwlodarczyk / 1200 explain
Created August 12, 2019 12:14
"Append (cost=0.00..3723.51 rows=952 width=20) (actual time=53.127..53.127 rows=0 loops=1)"
" -> Seq Scan on "public.book_1-1000_1990-01-01" (cost=0.00..31.25 rows=8 width=20) (actual time=0.718..0.718 rows=0 loops=1)"
" Filter: (id_owner = 4)"
" -> Seq Scan on "public.book_1-1000_1990-04-01" (cost=0.00..31.25 rows=8 width=20) (actual time=0.527..0.527 rows=0 loops=1)"
" Filter: (id_owner = 4)"
" -> Seq Scan on "public.book_1-1000_1990-07-01" (cost=0.00..31.25 rows=8 width=20) (actual time=0.513..0.513 rows=0 loops=1)"
" Filter: (id_owner = 4)"
" -> Seq Scan on "public.book_1-1000_1990-10-01" (cost=0.00..31.25 rows=8 width=20) (actual time=0.453..0.453 rows=0 loops=1)"
" Filter: (id_owner = 4)"
" -> Seq Scan on "public.book_1-1000_1991-01-01" (cost=0.00..31.25 rows=8 width=20) (actual time=0.417..0.418 rows=0 loops=1)"
@piotrwlodarczyk
piotrwlodarczyk / 6000 partitions
Created August 12, 2019 12:14
CREATE TABLE "public.book_1-1000" PARTITION OF public.book FOR VALUES FROM ('1') TO ('1000') PARTITION BY RANGE (added);
CREATE TABLE "public.book_1-1000_1990-01-01" PARTITION OF "public.book_1-1000" FOR VALUES FROM ('1990-01-01') TO ('1990-04-01');
CREATE TABLE "public.book_1-1000_1990-04-01" PARTITION OF "public.book_1-1000" FOR VALUES FROM ('1990-04-01') TO ('1990-07-01');
CREATE TABLE "public.book_1-1000_1990-07-01" PARTITION OF "public.book_1-1000" FOR VALUES FROM ('1990-07-01') TO ('1990-10-01');
CREATE TABLE "public.book_1-1000_1990-10-01" PARTITION OF "public.book_1-1000" FOR VALUES FROM ('1990-10-01') TO ('1991-01-01');
CREATE TABLE "public.book_1-1000_1991-01-01" PARTITION OF "public.book_1-1000" FOR VALUES FROM ('1991-01-01') TO ('1991-04-01');
CREATE TABLE "public.book_1-1000_1991-04-01" PARTITION OF "public.book_1-1000" FOR VALUES FROM ('1991-04-01') TO ('1991-07-01');
CREATE TABLE "public.book_1-1000_1991-07-01" PARTITION OF "public.book_1-1000" FOR VALUES FROM ('1991-07-01') TO ('1991-10