Skip to content

Instantly share code, notes, and snippets.

Avatar
💭
I may be slow to respond.

Aleksey Leshchuk alekseyl

💭
I may be slow to respond.
View GitHub Profile
@alekseyl
alekseyl / table.md
Last active Dec 14, 2017
anycable supported features
View table.md
Feature Status
Connection Identifiers +
Connection Request (cookies, params) +
Disconnect Handling +
Subscribe to channels +
Parameterized subscriptions +
Unsubscribe from channels +
Subscription Instance Variables -
Performing Channel Actions +
View text_pattern_ops6.sql
EXPLAIN ANALYZE SELECT * FROM words WHERE word LIKE 'o%' ORDER BY word USING ~<~ LIMIT 10;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=0.42..36.23 rows=10 width=440) (actual time=0.135..0.189 rows=10 loops=1)
-> Index Scan using index_words_word on words (cost=0.42..30333.13 rows=8471 width=440) (actual time=0.133..0.185 rows=10 loops=1)
Index Cond: (((word)::text ~>=~ 'o'::text) AND ((word)::text ~<~ 'p'::text))
Filter: ((word)::text ~~ 'o%'::text)
Planning time: 0.299 ms
Execution time: 0.216 ms
View text_pattern_ops5.sql
EXPLAIN ANALYZE SELECT * FROM words WHERE word LIKE 'o%' ORDER BY word USING < LIMIT 10;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=20178.09..20178.12 rows=10 width=440) (actual time=45.203..45.215 rows=10 loops=1)
-> Sort (cost=20178.09..20199.27 rows=8471 width=440) (actual time=45.202..45.203 rows=10 loops=1)
Sort Key: word
Sort Method: top-N heapsort Memory: 29kB
-> Bitmap Heap Scan on words (cost=218.41..19995.04 rows=8471 width=440) (actual time=4.769..26.591 rows=11599 loops=1)
Filter: ((word)::text ~~ 'o%'::text)
Heap Blocks: exact=1192
View varchar_pattern_example4.sql
EXPLAIN ANALYZE SELECT * FROM words WHERE word LIKE 'o%' ORDER BY word LIMIT 10;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=20178.09..20178.12 rows=10 width=440) (actual time=123.264..123.267 rows=10 loops=1)
-> Sort (cost=20178.09..20199.27 rows=8471 width=440) (actual time=123.263..123.264 rows=10 loops=1)
Sort Key: word
Sort Method: top-N heapsort Memory: 29kB
-> Bitmap Heap Scan on words (cost=218.41..19995.04 rows=8471 width=440) (actual time=12.194..97.945 rows=11599 loops=1)
Filter: ((word)::text ~~ 'o%'::text)
Heap Blocks: exact=1192
View var_char_patter_ops3.sql
EXPLAIN ANALYZE SELECT * FROM words WHERE word LIKE 'o%' LIMIT 10;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=0.42..36.23 rows=10 width=440) (actual time=1.343..1.473 rows=10 loops=1)
-> Index Scan using index_words_word on words (cost=0.42..30333.13 rows=8471 width=440) (actual time=1.341..1.468 rows=10 loops=1)
Index Cond: (((word)::text ~>=~ 'o'::text) AND ((word)::text ~<~ 'p'::text))
Filter: ((word)::text ~~ 'o%'::text)
Planning time: 0.207 ms
Execution time: 1.521 ms
View varchar_pattern_example2.sql
EXPLAIN ANALYZE SELECT * FROM words WHERE word = 'test';
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------
Index Scan using index_words_word on words (cost=0.42..8.44 rows=1 width=440) (actual time=1.628..1.631 rows=1 loops=1)
Index Cond: ((word)::text = 'test'::text)
Planning time: 0.269 ms
Execution time: 1.686 ms
View broken_like_example.sql
EXPLAIN ANALYZE SELECT * FROM words WHERE word LIKE 't%' LIMIT 10;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------
Limit (cost=0.00..32.10 rows=10 width=440) (actual time=68.487..100.961 rows=10 loops=1)
-> Seq Scan on words (cost=0.00..40795.61 rows=12707 width=440) (actual time=68.485..100.959 rows=10 loops=1)
Filter: ((word)::text ~~ 't%'::text)
Rows Removed by Filter: 181269
Planning time: 7.296 ms
Execution time: 101.007 ms
View postgres_derailed_GIN_2.sql
EXPLAIN ANALYZE SELECT id, title FROM "cards"
WHERE (title_tsv @@ to_tsquery( 'english', '(o:*|o)')) AND collection_id = 624::bigint
LIMIT 10;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=64.38..103.76 rows=10 width=57) (actual time=70.233..70.250 rows=10 loops=1)
-> Bitmap Heap Scan on cards (cost=64.38..210.07 rows=37 width=57) (actual time=70.231..70.248 rows=10 loops=1)
Recheck Cond: ((title_tsv @@ '''o'':* | ''o'''::tsquery) AND (collection_id = '624'::bigint))
Heap Blocks: exact=10
-> Bitmap Index Scan on index_examples_fts_with_collection (cost=0.00..64.37 rows=37 width=0) (actual time=70.204..70.204 rows=131 loops=1)
@alekseyl
alekseyl / derailed_index_example.sql
Last active Nov 23, 2017
derailed postgres index example on GIN without datatype convertion
View derailed_index_example.sql
EXPLAIN ANALYZE SELECT id, title FROM "cards"
WHERE (title_tsv @@ to_tsquery( 'english', '(o:*|o)')) AND collection_id = 624
LIMIT 10;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=408.55..447.93 rows=10 width=57) (actual time=273.000..273.103 rows=10 loops=1)
-> Bitmap Heap Scan on cards (cost=408.55..554.24 rows=37 width=57) (actual time=272.999..273.101 rows=10 loops=1)
Recheck Cond: ((collection_id = 624) AND (title_tsv @@ '''o'':* | ''o'''::tsquery) )
Heap Blocks: exact=10
-> BitmapAnd (cost=408.55..408.55 rows=37 width=0) (actual time=272.973..272.973 rows=0 loops=1)
View swagger-blocks-example.rb
class AutocompleteControllerDoc
include Swagger::Blocks
swagger_path '/autocomplete.json' do
operation :get,
summary: 'Fetches autocompletes on Cards titles and tags',
description: 'Returns nearest completions for words in card titles and tags,'\
' respects card restrictions and privacy rules',
tags: ['autocomple'] do