Re: [PERFORM] slow seqscan

[Edoardo Ceccarelli]

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';
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:

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
Edoardo
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
   (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])