Re: slow seqscan

>
> 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 with index space not being
> reclaimed in a vacuum. I believe this was fixed in 7.4. By not
> reclaiming the space the indexes grow larger and larger over time,
> causing PG to prefer a sequential scan over an index scan (I think).
>
>

The query is this:
SELECT *, oid FROM annuncio400
WHERE rubric = 'DD' AND LOWER(testo) Like LOWER('cbr%')
OFFSET 0 LIMIT 11

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=51.47..56.42
rows=11 loops=1)
-> Seq Scan on annuncio400 (cost=0.00..35490.60 rows=125 width=546)
(actual time=51.47..56.40 rows=12 loops=1)
Filter: ((rubric = 'DD'::bpchar) AND (lower((testo)::text) ~~
'cbr%'::text))
Total runtime: 56.53 msec
(4 rows)

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!!!
check this (same table, same query, different rubric=MA index):

dba400=# explain analyze SELECT *, oid FROM annuncio400 WHERE rubric =
'MA' AND LOWER(testo) Like LOWER('cbr%') OFFSET 0 LIMIT 11;
QUERY
PLAN
-------------------------------------------------------------------------------------------------------------------------------

Limit (cost=0.00..6630.72 rows=9 width=546) (actual time=42.74..42.74
rows=0 loops=1)
-> Index Scan using rubric on annuncio400 (cost=0.00..6968.48 rows=9
width=546) (actual time=42.73..42.73 rows=0 loops=1)
Index Cond: (rubric = 'MA'::bpchar)
Filter: (lower((testo)::text) ~~ 'cbr%'::text)
Total runtime: 42.81 msec
(5 rows)

Thanks for your help
Edoardo

>
>