X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id 43E9FD1BA8F for ; Wed, 21 Apr 2004 13:23:27 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 25305-07 for ; Wed, 21 Apr 2004 13:23:23 -0300 (ADT) Received: from smtp1.aruba.it (smtp1.aruba.it [62.149.128.200]) by svr1.postgresql.org (Postfix) with SMTP id 75B59D1DF75 for ; Wed, 21 Apr 2004 13:23:22 -0300 (ADT) Received: (qmail 14567 invoked from network); 21 Apr 2004 16:23:10 -0000 Received: from unknown (HELO axa.it) (80.183.237.70) by smtp1.aruba.it with SMTP; 21 Apr 2004 16:23:10 -0000 Message-ID: <40869FF0.6090100@axa.it> Date: Wed, 21 Apr 2004 18:23:12 +0200 From: Edoardo Ceccarelli User-Agent: Mozilla Thunderbird 0.5 (Windows/20040207) X-Accept-Language: it, en, en-us MIME-Version: 1.0 To: Stephan Szabo Cc: Christopher Kings-Lynne , Nick Barr , pgsql-performance@postgresql.org Subject: Re: slow seqscan 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> In-Reply-To: <20040421072156.K55057@megazone.bigpanda.com> Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Spam-Rating: smtp1.aruba.it 1.6.2 0/1000/N X-Virus-Scanned: by amavisd-new at postgresql.org X-Spam-Status: No, hits=0.2 tagged_above=0.0 required=5.0 tests=RCVD_IN_NJABL, RCVD_IN_SORBS X-Spam-Level: X-Archive-Number: 200404/354 X-Sequence-Number: 6654 >>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