pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Edoardo Ceccarelli <eddy@axa.it>
To: Nick Barr <nicky@chuckie.co.uk>
Cc: pgsql-performance@postgresql.org
Subject: Re: slow seqscan
Date: Wed, 21 Apr 2004 10:34:48 +0200
Message-ID: <40863228.907@axa.it> (raw)
In-Reply-To: <4086272E.2080102@chuckie.co.uk>
References: <40862003.2090201@axa.it>
	<4086272E.2080102@chuckie.co.uk>


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

>
>



view thread (12+ messages)  latest in thread

Message-ID: <40863228.907@axa.it>
Permalink:  ../40863228.907@axa.it/
Also on:    postgresql.org/message-id/40863228.907@axa.it

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-performance@postgresql.org
  Cc: eddy@axa.it, nicky@chuckie.co.uk
  Subject: Re: slow seqscan
  In-Reply-To: <40863228.907@axa.it>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox