pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Edoardo Ceccarelli <eddy@axa.it>
To: Stephan Szabo <sszabo@megazone.bigpanda.com>
Cc: Christopher Kings-Lynne <chriskl@familyhealth.com.au>
Cc: Nick Barr <nicky@chuckie.co.uk>
Cc: pgsql-performance@postgresql.org
Subject: Re: slow seqscan
Date: Wed, 21 Apr 2004 18:23:12 +0200
Message-ID: <40869FF0.6090100@axa.it> (raw)
In-Reply-To: <20040421072156.K55057@megazone.bigpanda.com>
References: <40862003.2090201@axa.it>
	<4086272E.2080102@chuckie.co.uk>
	<40863228.907@axa.it>
	<408636A5.2050305@familyhealth.com.au>
	<408641B7.5090808@axa.it>
	<20040421072156.K55057@megazone.bigpanda.com>


>>can't understand this policy:
>>
>>dba400=# SELECT count(*) from annuncio400 where rubric='DD';
>> count
>>-------
>>  6753
>>(1 row)
>>
>>dba400=# SELECT count(*) from annuncio400 where rubric='MA';
>> count
>>-------
>>  2165
>>(1 row)
>>
>>so it's using the index on 2000 rows and not for 6000?  it's not that
>>big difference, isn't it?
>>    
>>
>
>It's a question of how many pages it thinks it's going to have to retrieve
>in order to handle the request.  If it say needs (or think it needs) to
>retrieve 50% of the pages, then given a random_page_cost of 4, it's going
>to expect the index scan to be about twice the cost.
>
>Generally speaking one good way to compare is to try the query with
>explain analyze and then change parameters like enable_seqscan and try the
>query with explain analyze again and compare the estimated rows and costs.
>That'll give an idea of how it expects the two versions of the query to
>compare speed wise.
>
>
>  
>
Ok then how do you explain this?
just created a copy of the same database

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)


anyway, shall I try to lower the random_page value since I get an index 
scan? I mean that in my case I've already noted that with index scan 
that query get executed in 1/10 of the seqscan speed.

Thank you
Edoardo



view thread (12+ messages)  latest in thread

Message-ID: <40869FF0.6090100@axa.it>
Permalink:  ../40869FF0.6090100@axa.it/
Also on:    postgresql.org/message-id/40869FF0.6090100@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, sszabo@megazone.bigpanda.com, chriskl@familyhealth.com.au, nicky@chuckie.co.uk
  Subject: Re: slow seqscan
  In-Reply-To: <40869FF0.6090100@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