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

--- Martijn van Oosterhout <kleptog@svana.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