Hello Postgresql users,
In my setting, I found that sometimes the query does not use the gin index built for a tsv column.
Attached file provide more info (with explain analyze).
Thanks and regards,
Patrick
jobs=> explain analyze SELECT company,industry FROM jobs WHERE tsv @@ to_tsquery('english', 'travel');
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------
Append (cost=0.00..35337.69 rows=61625 width=67) (actual time=0.122..59909.736 rows=61114 loops=1)
-> Seq Scan on jobs (cost=0.00..0.00 rows=1 width=64) (actual time=0.001..0.001 rows=0 loops=1)
Filter: (tsv @@ '''travel'''::tsquery)
-> Seq Scan on jobs_2014p (cost=0.00..25629.21 rows=46735 width=64) (actual time=0.121..47191.053 rows=46142 loops=1)
Filter: (tsv @@ '''travel'''::tsquery)
Rows Removed by Filter: 243194
-> Seq Scan on jobs_2013p (cost=0.00..9708.48 rows=14889 width=75) (actual time=5.325..12708.878 rows=14972 loops=1)
Filter: (tsv @@ '''travel'''::tsquery)
Rows Removed by Filter: 86893
Total runtime: 59917.092 ms
(10 rows)
jobs=> set random_page_cost = 2;
SET
jobs=> explain analyze SELECT company,industry FROM jobs WHERE tsv @@ to_tsquery('english', 'travel');
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------
Append (cost=0.00..34676.86 rows=61625 width=67) (actual time=280.185..2151.618 rows=61117 loops=1)
-> Seq Scan on jobs (cost=0.00..0.00 rows=1 width=64) (actual time=0.001..0.001 rows=0 loops=1)
Filter: (tsv @@ '''travel'''::tsquery)
-> Bitmap Heap Scan on jobs_2014p (cost=2352.20..24968.38 rows=46735 width=64) (actual time=280.184..1784.566 rows=46143 loops=1)
Recheck Cond: (tsv @@ '''travel'''::tsquery)
-> Bitmap Index Scan on jobs_2014p_tsv_gin_idx (cost=0.00..2340.51 rows=46735 width=0) (actual time=277.210..277.210 rows=46143 loops=1)
Index Cond: (tsv @@ '''travel'''::tsquery)
-> Seq Scan on jobs_2013p (cost=0.00..9708.48 rows=14889 width=75) (actual time=0.069..361.839 rows=14974 loops=1)
Filter: (tsv @@ '''travel'''::tsquery)
Rows Removed by Filter: 86908
Total runtime: 2154.907 ms
(11 rows)
jobs=> set random_page_cost = 4;
SET
jobs=> explain analyze SELECT company,industry FROM jobs WHERE tsv @@ to_tsquery('english', 'travel');
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------
Append (cost=0.00..35337.69 rows=61625 width=67) (actual time=0.229..3462.236 rows=61117 loops=1)
-> Seq Scan on jobs (cost=0.00..0.00 rows=1 width=64) (actual time=0.001..0.001 rows=0 loops=1)
Filter: (tsv @@ '''travel'''::tsquery)
-> Seq Scan on jobs_2014p (cost=0.00..25629.21 rows=46735 width=64) (actual time=0.228..2898.271 rows=46143 loops=1)
Filter: (tsv @@ '''travel'''::tsquery)
Rows Removed by Filter: 243213
-> Seq Scan on jobs_2013p (cost=0.00..9708.48 rows=14889 width=75) (actual time=44.810..556.103 rows=14974 loops=1)
Filter: (tsv @@ '''travel'''::tsquery)
Rows Removed by Filter: 86912
Total runtime: 3468.134 ms
(10 rows)
-- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
