Skip to content

Instantly share code, notes, and snippets.

View mkubala's full-sized avatar

Marcin Kubala mkubala

30 followers · 18 following
  • SoftwareMill
  • Kraków, Poland
  • 02:40 (UTC +02:00)
View GitHub Profile
@mkubala
mkubala / execution_plan_gin_int_ops_indexed_tags
Created July 10, 2020 11:56
explain analyze select * from journal where deleted = false and tags @> ARRAY[3] and ordering > 1000 and ordering <= 3000 ORDER BY ordering asc limit 1000;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=41918.26..41918.31 rows=20 width=83) (actual time=911.388..911.421 rows=200 loops=1)
-> Sort (cost=41918.26..41918.31 rows=20 width=83) (actual time=911.387..911.402 rows=200 loops=1)
Sort Key: journal_0_0.ordering
Sort Method: quicksort Memory: 53kB
-> Append (cost=483.28..41917.83 rows=20 width=83) (actual time=13.268..911.356 rows=200 loops=1)
-> Bitmap Heap Scan on journal_0_0 (cost=483.28..727.81 rows=1 width=54) (actual time=3.420..3.420 rows=0 loops=1)
Recheck Cond: ((ordering > 1000) AND (ordering <= 3000) AND (tag
@mkubala
mkubala / third_query_run
Created July 10, 2020 11:55
psql> select * from journal where deleted = false and tags @> ARRAY[3] and ordering > 1000 and ordering <= 3000 ORDER BY ordering asc limit 1000;
ordering | sequence_number | deleted | persistence_id | message | tags
----------+-----------------+---------+----------------+----------+---------
1003 | 100 | f | p-3 | \x348b31 | {3,101}
1013 | 101 | f | p-3 | \x348b31 | {3,102}
1023 | 102 | f | p-3 | \x348b31 | {3,103}
…
Time: 1129.634 ms (00:01.130)
@mkubala
mkubala / introduce_intarray.sql
Created July 10, 2020 11:54
-- Loads the module
CREATE EXTENSION intarray;
-- Recreate the index
DROP INDEX journal_tags_idx;
CREATE INDEX journal_tags_idx ON public.journal USING GIN(tags gin__int_ops);
@mkubala
mkubala / execution_plan_gin_indexed_tags
Created July 10, 2020 11:53
explain analyze select * from journal where deleted = false and tags @> ARRAY[3] and ordering > 1000 and ordering <= 3000 ORDER BY ordering asc limit 1000;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=352320.62..352320.67 rows=20 width=83) (actual time=9310.650..9310.683 rows=200 loops=1)
-> Sort (cost=352320.62..352320.67 rows=20 width=83) (actual time=9310.649..9310.664 rows=200 loops=1)
Sort Key: journal_0_0.ordering
Sort Method: quicksort Memory: 53kB
-> Append (cost=12.03..352320.19 rows=20 width=83) (actual time=2834.936..9310.622 rows=200 loops=1)
-> Bitmap Heap Scan on journal_0_0 (cost=12.03..35212.03 rows=1 width=54) (actual time=921.642..921.642 rows=0 loops=1)
Recheck Cond: ((ordering > 1000) AND (ordering <= 3
@mkubala
mkubala / create_gin_index.sql
Created July 10, 2020 11:52
CREATE INDEX journal_tags_idx ON public.journal USING GIN (tags);
@mkubala
mkubala / execution_plan_non_indexed_tags
Created July 10, 2020 11:51
explain analyze select * from journal where deleted = false and tags @> ARRAY[3] and ordering > 1000 and ordering <= 3000 ORDER BY ordering asc limit 1000;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=332499.55..332499.60 rows=20 width=325) (actual time=9742.235..9742.267 rows=200 loops=1)
-> Sort (cost=332499.55..332499.60 rows=20 width=325) (actual time=9742.234..9742.249 rows=200 loops=1)
Sort Key: journal_0_0.ordering
Sort Method: quicksort Memory: 53kB
-> Append (cost=12.03..332499.12 rows=20 width=325) (actual time=2902.069..9742.207 rows=200 loops=1)
-> Bitmap Heap Scan on journal_0_0 (cost=12.03..33237.63 rows=1 width=54) (actual time=992.693..992.693 rows=0 loops=1)
Recheck Cond: ((ordering > 1000) AND (
@mkubala
mkubala / events_by_tag_query.sql
Created July 10, 2020 11:50
SELECT * FROM public.journal WHERE deleted = false AND tags @> ARRAY[3] AND ordering > 1000 AND ordering <= 3000 ORDER BY ordering ASC LIMIT 1000;
@mkubala
mkubala / journal_partition_schema
Created July 10, 2020 11:48
psql> \d+ journal_0
Partitioned table “public.journal_0"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
-----------------+-----------+-----------+----------+-----------------------------------------------+----------+--------------+-------------
ordering | bigint | | not null | nextval('journal_ordering_seq'::regclass) | plain | |
sequence_number | bigint | | not null | | plain | |
deleted | boolean | | not null | false | plain | |
persistence_id | text | | not null | | extended | |
message | bytea | | not null | | extended | |
@mkubala
mkubala / journal_toplevel_schema
Last active July 10, 2020 11:49
psql> \d+ journal
Partitioned table "public.journal"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
-----------------+-----------+-----------+----------+-----------------------------------------------+----------+--------------+-------------
ordering | bigint | | not null | nextval('journal_ordering_seq'::regclass) | plain | |
sequence_number | bigint | | not null | | plain | |
deleted | boolean | | not null | false | plain | |
persistence_id | text | | not null | | extended | |
message | bytea | | not null | | extended | |
ta
@mkubala
mkubala / keybase.md
Created June 9, 2020 06:59

Keybase proof

I hereby claim:

  • I am mkubala on github.
  • I am mkubala (https://keybase.io/mkubala) on keybase.
  • I have a public key ASDR5WCVFAepuSkvhNSR82FgoBwh8t9XmDLI15MBYakSHAo

To claim this, I am signing this object: