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
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
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
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
dba400=# explain analyze SELECT *, oid FROM annuncio400 WHERE rubric =
'DD' AND LOWER(testo) Like LOWER('cbr%') OFFSET 0 LIMIT 11;
QUERY
PLAN
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
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));
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:
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
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
10 matches
Mail list logo