In general we are going to need more information, like what kind of search filters you are using on the text field and an EXPLAIN ANALYZE. But can you try and run the following, bearing in mind it will take a while to complete.
REINDEX TABLE <table_name>
From what I remember there were issues with index space not being reclaimed in a vacuum. I believe this was fixed in 7.4. By not reclaiming the space the indexes grow larger and larger over time, causing PG to prefer a sequential scan over an index scan (I think).
The query is this: SELECT *, oid FROM annuncio400 WHERE rubric = 'DD' AND LOWER(testo) Like LOWER('cbr%') OFFSET 0 LIMIT 11
dba400=# explain analyze SELECT *, oid FROM annuncio400 WHERE rubric = 'DD' AND LOWER(testo) Like LOWER('cbr%') OFFSET 0 LIMIT 11;
QUERY PLAN --------------------------------------------------------------------------------------------------------------------
Limit (cost=0.00..3116.00 rows=11 width=546) (actual time=51.47..56.42 rows=11 loops=1)
-> Seq Scan on annuncio400 (cost=0.00..35490.60 rows=125 width=546) (actual time=51.47..56.40 rows=12 loops=1)
Filter: ((rubric = 'DD'::bpchar) AND (lower((testo)::text) ~~ 'cbr%'::text))
Total runtime: 56.53 msec
(4 rows)
But the strangest thing ever is that if I change the filter with another one that represent a smaller amount of data it uses the index scan!!!
check this (same table, same query, different rubric=MA index):
dba400=# explain analyze SELECT *, oid FROM annuncio400 WHERE rubric = 'MA' AND LOWER(testo) Like LOWER('cbr%') OFFSET 0 LIMIT 11; QUERY PLAN -------------------------------------------------------------------------------------------------------------------------------
Limit (cost=0.00..6630.72 rows=9 width=546) (actual time=42.74..42.74 rows=0 loops=1)
-> Index Scan using rubric on annuncio400 (cost=0.00..6968.48 rows=9 width=546) (actual time=42.73..42.73 rows=0 loops=1)
Index Cond: (rubric = 'MA'::bpchar)
Filter: (lower((testo)::text) ~~ 'cbr%'::text)
Total runtime: 42.81 msec
(5 rows)
Thanks for your help Edoardo
---------------------------(end of broadcast)--------------------------- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
