On 12/30/2015 10:09 AM, Tom Lane wrote:
Andy Colson <andy@xxxxxxxxxxxxxxx> writes:
-> Bitmap Index Scan on search_key (cost=0.00..6.00 rows=1 width=0) (actual time=0.025..0.025 rows=0 loops=1)
Index Cond: (search_vec @@ to_tsquery('213 & E & 13 & ST & N'::text))
Hmm ... so the partial case actually is significantly more expensive than
the non-partial case: 4 msec vs .025 msec. Still, that's about a 200x
penalty, not the 10000x penalty the planner is ascribing to it.
Thanks for the data! I'll go moan about this on -hackers.
regards, tom lane
No, that's not right, the table was empty. I rebuilt the table as it
was before, here are all three queries again:
explain analyze
select *
from search
where search_vec @@ to_tsquery('213 & E & 13 & ST & N:*');
QUERY PLAN
-------------------------------------------------------------------------------------------------------
Seq Scan on search (cost=0.00..2211.08 rows=1 width=73) (actual
time=31.904..380.568 rows=1 loops=1)
Filter: (search_vec @@ to_tsquery('213 & E & 13 & ST & N:*'::text))
Rows Removed by Filter: 79071
Total runtime: 380.609 ms
set enable_seqscan TO 0;
explain analyze
select *
from search
where search_vec @@ to_tsquery('213 & E & 13 & ST & N:*');
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on search (cost=63623.00..63624.02 rows=1 width=73)
(actual time=5.004..5.004 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..63623.00 rows=1
width=0) (actual time=4.996..4.996 rows=1 loops=1)
Index Cond: (search_vec @@ to_tsquery('213 & E & 13 & ST &
N:*'::text))
Total runtime: 5.045 ms
explain analyze
select *
from search
where search_vec @@ to_tsquery('213 & E & 13 & ST & N');
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on search (cost=23.00..24.02 rows=1 width=73)
(actual time=4.067..4.067 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..23.00 rows=1
width=0) (actual time=4.057..4.057 rows=1 loops=1)
Index Cond: (search_vec @@ to_tsquery('213 & E & 13 & ST &
N'::text))
Total runtime: 4.109 ms
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
