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;
QUERY
PLAN
--------------------------------------------------------------------------------------------------------------------
Limit (cost=0.00..3116.00 rows=11 width=546) (actual time=46.66..51.40
rows=11 loops=1)
-> Seq Scan on annuncio400 (cost=0.00..35490.60 rows=125 width=546)
(actual time=46.66..51.38 rows=12 loops=1)
Filter: ((rubric = 'DD'::bpchar) AND (lower((testo)::text) ~~
'cbr%'::text))
Total runtime: 51.46 msec
(4 rows)
fastest index scan query on dba400b (exact copy of dba400)
dba400b=# explain analyze SELECT *, oid FROM annuncio400 WHERE rubric =
'DD' AND LOWER(testo) Like LOWER('cbr%') OFFSET 0 LIMIT 11;
QUERY
PLAN
------------------------------------------------------------------------------------------------------------------------------
Limit (cost=0.00..7058.40 rows=9 width=546) (actual time=1.36..8.18
rows=11 loops=1)
-> Index Scan using rubric on annuncio400 (cost=0.00..7369.42
rows=9 width=546) (actual time=1.35..8.15 rows=12 loops=1)
Index Cond: (rubric = 'DD'::bpchar)
Filter: (lower((testo)::text) ~~ 'cbr%'::text)
Total runtime: 8.28 msec
(5 rows)
what about this index you suggested? it gives me sintax error while
trying to create it:
CREATE INDEX annuncio400_rubric_testo_idx ON annuncio400(rubric,
LOWER(testo));
Thanks
Edoardo
Christopher Kings-Lynne ha scritto:
>
>> 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: the planner will ignore your desire to choose an index scan if
> your
> joining column's datatypes do not match
>
>
>