Skip to content

Instantly share code, notes, and snippets.

View alekseyl's full-sized avatar
👨‍💻

Aleksey Leshchuk alekseyl

👨‍💻
12 followers · 1 following
View GitHub Profile
@alekseyl
alekseyl / postgres_derailed_GIN_2.sql
Last active November 23, 2017 14:38
Fixed plan.
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 / broken_like_example.sql
Last active November 24, 2017 11:12
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
@alekseyl
alekseyl / varchar_pattern_example2.sql
Created November 24, 2017 11:31
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
@alekseyl
alekseyl / var_char_patter_ops3.sql
Created November 24, 2017 12:47
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
@alekseyl
alekseyl / varchar_pattern_example4.sql
Created November 24, 2017 12:49
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
@alekseyl
alekseyl / text_pattern_ops5.sql
Created November 24, 2017 13:35
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
@alekseyl
alekseyl / text_pattern_ops6.sql
Created November 24, 2017 13:45
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
@alekseyl
alekseyl / table.md
Last active September 17, 2021 07:43
anycable supported features
Feature Status
Connection identifiers ✅*
Connection request data (cookies, params) ✅
Disconnect handling ✅
Subscribe to channels ✅
Parameterized subscriptions ✅
Unsubscribe from channels ✅
Subscription Instance Variables ✅ **
Performing Channel Actions ✅
@alekseyl
alekseyl / devise_anycable_substitution.rb
Created December 15, 2017 07:50
devise anycable substitution example
env['rack.session'] = ActionDispatch::Cookies::EncryptedCookieJar.new(cookies)[:_application_session]
current_user = Warden::SessionSerializer.new(env).fetch(:user)
@alekseyl
alekseyl / gist:8abc0d088f30602b44a02d1723fc26bb
Last active March 5, 2018 06:54
Comparision of random samples
+----------------+-----------------+-----------------+-----------------+--------------+
| Array size | order by random | random() < 0.xx | generate_series | M partials |
| 10 | 0.25ms / 1.35 | 0.23ms / 1.2 | 0.12ms / 0.64 | 0.19ms / 1.0 |
| 100 | 0.58ms / 2.85 | 0.41ms / 2.04 | 0.14ms / 0.69 | 0.2ms / 1.0 |
| 1000 | 3.88ms / 7.3 | 2.49ms / 4.7 | 0.47ms / 0.88 | 0.53ms / 1.0 |
| 10000 | 45.59ms / 15.57 | 29.09ms / 9.94 | 2.87ms / 0.98 | 2.93ms / 1.0 |
+----------------+-----------------+-----------------+-----------------+--------------+