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

Re: slow seqscan

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

In response to

pgsql-performance by date

Next:From: Dirk Lutzeb├ĄckDate: 2004-04-21 12:10:55
Subject: Re: Wierd context-switching issue on Xeon
Previous:From: ohpDate: 2004-04-21 11:18:53
Subject: Re: Wierd context-switching issue on Xeon

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