X-Original-To: pgsql-performance-postgresql.org@postgresql.org Received: from localhost (mx1.hub.org [200.46.208.251]) by postgresql.org (Postfix) with ESMTP id 2F3339FA2B4 for ; Sun, 11 Jun 2006 03:35:30 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024) with ESMTP id 61890-02 for ; Sun, 11 Jun 2006 03:35:21 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey- Received: from nz-out-0102.google.com (nz-out-0102.google.com [64.233.162.201]) by postgresql.org (Postfix) with ESMTP id 4C1979FA1A8 for ; Sun, 11 Jun 2006 03:35:20 -0300 (ADT) Received: by nz-out-0102.google.com with SMTP id s1so971038nze for ; Sat, 10 Jun 2006 23:35:20 -0700 (PDT) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:to:subject:mime-version:content-type; b=PkbIgeilC1FkgKHIN11s7PnXdhF8VW1WNszNL2fwYoyxgX6a/Dfx2zEgsM/q5MYIjFh0M5Sf1w2TOTt8MZPMCAZ3fdDfcDSIkiJ73Uvvn2eczMk/hlfsQ+BYWHbEWs9iyH4R5A5RoMW22YKGALXYJAG6w8jXHP5Uhp/4+1uCNGQ= Received: by 10.65.152.9 with SMTP id e9mr3971252qbo; Sat, 10 Jun 2006 23:35:20 -0700 (PDT) Received: by 10.65.147.19 with HTTP; Sat, 10 Jun 2006 23:35:20 -0700 (PDT) Message-ID: <5809b390606102335j41f734d8j@mail.gmail.com> Date: Sun, 11 Jun 2006 09:35:20 +0300 From: "John Top-k apad" To: pgsql-performance@postgresql.org Subject: Variation between query runtimes MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_41350_12071152.1150007720013" X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200606/116 X-Sequence-Number: 19473 ------=_Part_41350_12071152.1150007720013 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Content-Disposition: inline Hi, I have the following quering plans: "Seq Scan on ind_uni_100 (cost=0.00..27242.00 rows=1000000 width=104) (actual time=0.272..2444.667 rows=1000000 loops=1)" "Total runtime: 4229.449 ms" and "Bitmap Heap Scan on ind_uni_100 (cost=314.00..18181.00 rows=50000 width=104) (actual time=74.106..585.368 rows=49758 loops=1)" " Recheck Cond: (b = 1)" " -> Bitmap Index Scan on index_b_ind_uni_100 (cost=0.00..314.00rows=50000 width=0) (actual time= 61.814..61.814 rows=49758 loops=1)" " Index Cond: (b = 1)" "Total runtime: 638.787 ms" from pg_stast_get_blocks_fetched i can see that both queries need almost the same number of disk fetches which is quite reasonable ( the index is unclustered). But as you can see there is a great variation between query runtimes.Cansomeone explain this differnce? Thanks! ------=_Part_41350_12071152.1150007720013 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline Hi,

I have the following quering plans:

"Seq Scan on ind_uni_100 (cost=0.00..27242.00 rows=1000000 width=104) (actual time=0.272..2444.667 rows=1000000 loops=1)"
"Total runtime: 4229.449 ms"

and

"Bitmap Heap Scan on ind_uni_100  (cost=314.00..18181.00 rows=50000 width=104) (actual time=74.106..585.368 rows=49758 loops=1)"
"  Recheck Cond: (b = 1)"
"  ->  Bitmap Index Scan on index_b_ind_uni_100  (cost= 0.00..314.00 rows=50000 width=0) (actual time=61.814..61.814 rows=49758 loops=1)"
"        Index Cond: (b = 1)"
"Total runtime: 638.787 ms"

from pg_stast_get_blocks_fetched i can see that both queries need almost the same number of disk fetches which is quite reasonable ( the index is unclustered).

But as you can see there is a great variation between query runtimes.Can someone explain this differnce?

Thanks! ------=_Part_41350_12071152.1150007720013--