Re: cannot get stable function to use index
От | Andy Colson |
---|---|
Тема | Re: cannot get stable function to use index |
Дата | |
Msg-id | 56841C33.8090402@squeakycode.net обсуждение исходный текст |
Ответ на | Re: cannot get stable function to use index (Tom Lane <tgl@sss.pgh.pa.us>) |
Ответы |
Re: cannot get stable function to use index
(Tom Lane <tgl@sss.pgh.pa.us>)
|
Список | pgsql-general |
On 12/30/2015 10:09 AM, Tom Lane wrote: > Andy Colson <andy@squeakycode.net> 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
В списке pgsql-general по дате отправления: