tried the

enable_seqscan = false

and I'm having all index scans, timing has improved from 600ms to 18ms

wondering what other implications I might expect.

Edoardo Ceccarelli ha scritto:

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));

I wasn't able to make this 2 field index with lower:

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.

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.

can't understand this policy:

dba400=# SELECT count(*) from annuncio400 where rubric='DD';
(1 row)

dba400=# SELECT count(*) from annuncio400 where rubric='MA';
(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:

ALTER TABLE annuncio400 ALTER rubrik SET STATISTICS 100;
ANALYZE annuncio400;

done, almost the same, still not using index

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

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
   (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])

---------------------------(end of broadcast)--------------------------- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Reply via email to