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 inde
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
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 mak
just created a copy of the same database and it shows that is the
analyze that's messing things:
Slow seqscan query executed on dba400
dba400=# explain analyze SELECT *, oid FROM annuncio400 WHERE rubric =
'DD' AND LOWER(testo) Like LOWER('cbr%') OFFSET 0 LIMIT 11;
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: t
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));
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_id
dba400=# explain analyze SELECT *, oid FROM annuncio400 WHERE rubric =
'DD' AND LOWER(testo) Like LOWER('cbr%') OFFSET 0 LIMIT 11;
QUERY
PLAN
---
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
From what I remember there were issues with index
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
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
EXPLAIN
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
12 matches
Mail list logo
