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

Re: slow seqscan

From: Christopher Kings-Lynne <chriskl(at)familyhealth(dot)com(dot)au>
To: Edoardo Ceccarelli <eddy(at)axa(dot)it>
Cc: Nick Barr <nicky(at)chuckie(dot)co(dot)uk>, pgsql-performance(at)postgresql(dot)org
Subject: Re: slow seqscan
Date: 2004-04-21 08:53:57
Message-ID: 408636A5.2050305@familyhealth.com.au (view raw or flat)
Thread:
Lists: pgsql-performance
> 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)

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));

> 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!!!

What's strange about that?  The less data is going to be retrieved, the 
more likely postgres is to use the index.

I suggest maybe increasing the amount of stats recorded for your rubrik 
column:

ALTER TABLE annuncio400 ALTER rubrik SET STATISTICS 100;
ANALYZE annuncio400;

You could also try reducing the random_page_cost value in your 
postgresql.conf a little, say to 3 (if it's currently 4).  That will 
make postgres more likely to use index scans over seq scans.

Chris


In response to

Responses

pgsql-performance by date

Next:From: Edoardo CeccarelliDate: 2004-04-21 09:41:11
Subject: Re: slow seqscan
Previous:From: Edoardo CeccarelliDate: 2004-04-21 08:34:48
Subject: Re: slow seqscan

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