What happens if you go:I wasn't able to make this 2 field index with lower:
CREATE INDEX annuncio400_rubric_testo_idx ON annuncio400(rubric, LOWER(testo));
or even just:
CREATE INDEX annuncio400_rubric_testo_idx ON annuncio400(LOWER(testo));
dba400=# CREATE INDEX annuncio400_rubric_testo_idx ON annuncio400(rubric, LOWER(testo));
ERROR: parser: parse error at or near "(" at character 71
seems impossible to creat 2 field indexes with lower function.
The other one does not make it use the index.
can't understand this policy: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.
dba400=# SELECT count(*) from annuncio400 where rubric='DD'; count ------- 6753 (1 row)
dba400=# SELECT count(*) from annuncio400 where rubric='MA'; count ------- 2165 (1 row)
so it's using the index on 2000 rows and not for 6000? it's not that big difference, isn't it?
I suggest maybe increasing the amount of stats recorded for your rubrik column:done, almost the same, still not using index
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.
changed the setting on postgresql.conf, restarted the server, nothing has changed.
what about setting this to false? #enable_seqscan = true
thanks again Edoardo
---------------------------(end of broadcast)--------------------------- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])