Re: ltree + gist index performance degrades significantly over a night

--- Martijn van Oosterhout <kleptog(at)svana(dot)org> wrote:

> That's very odd. Like the other person said, do you vacuum and analyse?
> But my question is: is it using the index? What does EXPLAIN / EXPLAIN
> ANALYZE tell you?

data=# explain select * from search where search_vector ~ '*.o.r.l.*'::lquery;
QUERY PLAN
-------------------------------------------------------------------------------------------
Bitmap Heap Scan on search (cost=53.71..4566.65 rows=1345 width=161)
Recheck Cond: (search_vector ~ '*.o.r.l.*'::lquery)
-> Bitmap Index Scan on search_vector_idx (cost=0.00..53.71 rows=1345
width=0)
Index Cond: (search_vector ~ '*.o.r.l.*'::lquery)
(4 rows)

data=# explain analyze select * from search where search_vector ~
'*.o.r.l.*'::lquery;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on search (cost=53.71..4566.65 rows=1345 width=161) (actual
time=183684.156..196997.278 rows=1655 loops=1)
Recheck Cond: (search_vector ~ '*.o.r.l.*'::lquery)
-> Bitmap Index Scan on search_vector_idx (cost=0.00..53.71 rows=1345
width=0) (actual time=183683.857..183683.857 rows=1655 loops=1)
Index Cond: (search_vector ~ '*.o.r.l.*'::lquery)
Total runtime: 197000.061 ms
(5 rows)

I appreciate you taking the time to help me out. Thank you all.

__________________________________________________
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com