On 12/29/2015 6:35 PM, Tom Lane wrote:
Andy Colson <andy@xxxxxxxxxxxxxxx> writes:
I cannot get this sql to use the index:
explain analyze
select *
from search
where search_vec @@ to_tsquery_partial('213 E 13 ST N')
--------------------------------------------------------------------------
Seq Scan on search (cost=0.00..2526.56 rows=1 width=69) (actual
time=68.033..677.490 rows=1 loops=1)
Filter: (search_vec @@
to_tsquery((array_to_string('{213,E,13,ST,N}'::text[], ' & '::text) ||
':*'::text)))
Rows Removed by Filter: 76427
Total runtime: 677.548 ms
(4 rows)
If you force it with enable_seqscan = off, you'll soon see that it's
capable of picking the indexscan plan, but it doesn't want to because it
estimates that the cost will be much higher, which seems to be a
consequence of the ":*" in the query. (Even though the functions involved
are only stable, the planner is capable of seeing through them to look at
the pattern that will be fed to the GIN index search.) You get the same
results if you use the resulting tsquery without any function at all.
For example (with dummy data), I get
regression=# explain select * from search
where search_vec @@ '213 & e & 13 & st & n:*'::tsquery;
QUERY PLAN
----------------------------------------------------------------------------------
Seq Scan on search (cost=0.00..3774.01 rows=1 width=21)
Filter: (search_vec @@ '''213'' & ''e'' & ''13'' & ''st'' & ''n'':*'::tsquery)
(2 rows)
regression=# set enable_seqscan TO 0;
SET
regression=# explain select * from search
where search_vec @@ '213 & e & 13 & st & n:*'::tsquery;
QUERY PLAN
--------------------------------------------------------------------------------------------
Bitmap Heap Scan on search (cost=104444.00..104448.01 rows=1 width=21)
Recheck Cond: (search_vec @@ '''213'' & ''e'' & ''13'' & ''st'' & ''n'':*'::tsquery)
-> Bitmap Index Scan on search_key (cost=0.00..104444.00 rows=1 width=0)
Index Cond: (search_vec @@ '''213'' & ''e'' & ''13'' & ''st'' & ''n'':*'::tsquery)
(4 rows)
but for comparison, with a pattern without ':*', I get
regression=# explain select * from search
where search_vec @@ '213 & e & 13 & st & n'::tsquery;
QUERY PLAN
------------------------------------------------------------------------------------------
Bitmap Heap Scan on search (cost=44.00..48.01 rows=1 width=21)
Recheck Cond: (search_vec @@ '''213'' & ''e'' & ''13'' & ''st'' & ''n'''::tsquery)
-> Bitmap Index Scan on search_key (cost=0.00..44.00 rows=1 width=0)
Index Cond: (search_vec @@ '''213'' & ''e'' & ''13'' & ''st'' & ''n'''::tsquery)
(4 rows)
I'm inclined to think this is a bug in the estimator; it seems to be
charging for many more "entry page" fetches than there are pages in
the index. But maybe it's right and there will be lots of repeated
work involved. It would be interesting to see EXPLAIN ANALYZE results
from your data for these examples.
regards, tom lane
Here are my results, if there are any others you'd like to see please
let me know. Thanks Tom.
# explain analyze
select *
from search
where search_vec @@ to_tsquery('213 & E & 13 & ST & N:*');
QUERY PLAN
-------------------------------------------------------------------------------------------------------
Seq Scan on search (cost=0.00..2144.42 rows=1 width=69) (actual
time=30.584..361.147 rows=1 loops=1)
Filter: (search_vec @@ to_tsquery('213 & E & 13 & ST & N:*'::text))
Rows Removed by Filter: 76427
Total runtime: 361.181 ms
(4 rows)
Time: 363.012 ms
# set enable_seqscan TO 0;
SET
Time: 0.185 ms
# explain analyze
select *
from search
where search_vec @@ to_tsquery('213 & E & 13 & ST & N:*');
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on search (cost=63716.00..63717.02 rows=1 width=69)
(actual time=4.354..4.355 rows=1 loops=1)
Recheck Cond: (search_vec @@ to_tsquery('213 & E & 13 & ST &
N:*'::text))
-> Bitmap Index Scan on search_key (cost=0.00..63716.00 rows=1
width=0) (actual time=4.351..4.351 rows=1 loops=1)
Index Cond: (search_vec @@ to_tsquery('213 & E & 13 & ST &
N:*'::text))
Total runtime: 4.370 ms
(5 rows)
Time: 4.794 ms
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
