[PERFORM] slow seqscan

2004-04-21 Thread Edoardo Ceccarelli
My first post to this list :) Scenario: I have a database used only with search queries with only one table that holds about 450.000/500.000 records. The table is well indexed so that most of the queries are executed with index scan but since there is a big text field in the table (360chars) some

Re: [PERFORM] slow seqscan

2004-04-21 Thread Christopher Kings-Lynne
Hi Edoardo, The table is well indexed so that most of the queries are executed with index scan but since there is a big text field in the table (360chars) some search operation (with certain filters) ends up with seq scans. Please paste the exact SELECT query that uses a seqscan, plus the

Re: [PERFORM] slow seqscan

2004-04-21 Thread Nick Barr
Edoardo Ceccarelli wrote: My first post to this list :) Scenario: I have a database used only with search queries with only one table that holds about 450.000/500.000 records. The table is well indexed so that most of the queries are executed with index scan but since there is a big text field

Re: [PERFORM] slow seqscan

2004-04-21 Thread Edoardo Ceccarelli
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

Re: [PERFORM] slow seqscan

2004-04-21 Thread Christopher Kings-Lynne
dba400=# explain analyze SELECT *, oid FROM annuncio400 WHERE rubric = 'DD' AND LOWER(testo) Like LOWER('cbr%') OFFSET 0 LIMIT 11; QUERY PLAN

Re: [PERFORM] slow seqscan

2004-04-21 Thread 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

Re: [PERFORM] slow seqscan

2004-04-21 Thread Edoardo Ceccarelli
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));

Re: [PERFORM] slow seqscan

2004-04-21 Thread Christopher Kings-Lynne
enable_seqscan = false and I'm having all index scans, timing has improved from 600ms to 18ms wondering what other implications I might expect. Lots of really bad implications...it's really not a good idea. Chris ---(end of broadcast)--- TIP 9:

Re: [PERFORM] slow seqscan

2004-04-21 Thread Stephan Szabo
On Wed, 21 Apr 2004, Edoardo Ceccarelli wrote: 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

Re: [PERFORM] slow seqscan

2004-04-21 Thread Tom Lane
Edoardo Ceccarelli [EMAIL PROTECTED] writes: 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