Skip site navigation (1) Skip section navigation (2)

Re: slow seqscan

From: Edoardo Ceccarelli <eddy(at)axa(dot)it>
To: Nick Barr <nicky(at)chuckie(dot)co(dot)uk>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: slow seqscan
Date: 2004-04-21 08:34:48
Message-ID: 40863228.907@axa.it (view raw or flat)
Thread:
Lists: pgsql-performance
>
> 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

>
>

In response to

Responses

pgsql-performance by date

Next:From: Christopher Kings-LynneDate: 2004-04-21 08:53:57
Subject: Re: slow seqscan
Previous:From: Nick BarrDate: 2004-04-21 08:31:39
Subject: MySQL vs PG TPC-H benchmarks

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group