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)
What happens if you go:
CREATE INDEX annuncio400_rubric_testo_idx ON annuncio400(rubric, LOWER(testo));
or even just:
CREATE INDEX annuncio400_rubric_testo_idx ON annuncio400(LOWER(testo));
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!!!
What's strange about that? The less data is going to be retrieved, the more likely postgres is to use the index.
I suggest maybe increasing the amount of stats recorded for your rubrik column:
ALTER TABLE annuncio400 ALTER rubrik SET STATISTICS 100; ANALYZE annuncio400;
You could also try reducing the random_page_cost value in your postgresql.conf a little, say to 3 (if it's currently 4). That will make postgres more likely to use index scans over seq scans.
Chris
---------------------------(end of broadcast)--------------------------- TIP 8: explain analyze is your friend
