Skip to content

Instantly share code, notes, and snippets.

@alekseyl
Last active November 23, 2017 12:14
Show Gist options
  • Save alekseyl/22befe69f7a3d3f24ac37d8e27cce1bc to your computer and use it in GitHub Desktop.
Save alekseyl/22befe69f7a3d3f24ac37d8e27cce1bc to your computer and use it in GitHub Desktop.
derailed postgres index example on GIN without datatype convertion
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)
-> Bitmap Index Scan on index_cards_on_collection_id (cost=0.00..28.98 rows=1140 width=0) (actual time=0.333..0.333 rows=1128 loops=1)
Index Cond: (collection_id = 624)
-> Bitmap Index Scan on index_examples_fts_with_collection (cost=0.00..379.31 rows=37241 width=0) (actual time=272.521..272.521 rows=123114 loops=1)
Index Cond: (title_tsv @@ '''o'':* | ''o'''::tsquery)
Planning time: 3.457 ms
Execution time: 273.526 ms
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment