Received: from makus.postgresql.org (makus.postgresql.org [98.129.198.125])
	by mail.postgresql.org (Postfix) with ESMTP id 776F61BA1319
	for <pgsql-sql@postgresql.org>; Thu,  5 Apr 2012 09:03:17 -0300 (ADT)
Received: from mail-bk0-f46.google.com ([209.85.214.46])
	by makus.postgresql.org with esmtp (Exim 4.72)
	(envelope-from <m.p.dankoor@gmail.com>) id 1SFlPH-0001i0-SR
	for pgsql-sql@postgresql.org; Thu, 05 Apr 2012 12:03:17 +0000
Received: by bkcik5 with SMTP id ik5so1291016bkc.19
	for <pgsql-sql@postgresql.org>; Thu, 05 Apr 2012 05:03:02 -0700 (PDT)
DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20120113;
	h=message-id:date:from:user-agent:mime-version:to:subject:references
	:in-reply-to:content-type;
	bh=ETKNI6gaA/ekVn9zoXRGXQHD3xa/WAA6PuMmD0Eh0bc=;
	b=A7D9q2CIFmk2Wjk5kv63kUle65dIg6qvVJRboii3qAVIwXlyTrzTfwhC61pg6Sz1Uj
	qHoVRMYwiIpotxN8FgbraIfdQiG7GigyWbqeQ+8eyqA+ckwmoQTIs5kYJg2/KV4W1qcv
	8zOwy1dHOEA812JZsZz3TX9GrUtwtNrb7zQKPoaNjlo3EgQFA7MQzEo1mL29ZyvgrlCa
	KES6bvMdZapLZba6C/hg+CTPipbFKVt4hALpwe7LrlCmu3wyh2ouwYLbFSc94rFEJ9XH
	uuPGRCl80ftZ5PlkadG/Vmvd7YE8u4DyxuhwRPq5E6VLUVS3f7ilU1OGKdFXVekES6WG
	1++w==
Received: by 10.204.154.209 with SMTP id p17mr1106051bkw.6.1333627382711;
	Thu, 05 Apr 2012 05:03:02 -0700 (PDT)
Received: from [192.168.178.16] (qtuning.xs4all.nl. [80.101.37.209])
	by mx.google.com with ESMTPS id zx16sm8371547bkb.13.2012.04.05.05.03.01
	(version=SSLv3 cipher=OTHER); Thu, 05 Apr 2012 05:03:02 -0700 (PDT)
Message-ID: <4F7D89F5.7090301@gmail.com>
Date: Thu, 05 Apr 2012 14:03:01 +0200
From: Mario Dankoor <m.p.dankoor@gmail.com>
User-Agent: Mozilla/5.0 (Windows NT 6.1; WOW64;
	rv:11.0) Gecko/20120327 Thunderbird/11.0.1
MIME-Version: 1.0
To: pgsql-sql@postgresql.org
Subject: Re: bad plan
References: <4F7D843E.4060304@ulb.ac.be>
In-Reply-To: <4F7D843E.4060304@ulb.ac.be>
Content-Type: multipart/alternative;
	boundary="------------000908030109070706000309"
X-Pg-Spam-Score: -2.6 (--)
X-Archive-Number: 201204/10
X-Sequence-Number: 36487

This is a multi-part message in MIME format.
--------------000908030109070706000309
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit

Julien,

It looks like you forgot to post the query.

Mario
On 2012-04-05 1:38 PM, Julien Cigar wrote:
> Hello,
>
> I have an extremely bad plan for one of my colleague's query. 
> Basically PostgreSQL chooses to seq scan instead of index scan. This 
> is on:
>
> antabif=# select version();
>                                                  version
> ---------------------------------------------------------------------------------------------------------- 
>
>  PostgreSQL 9.0.7 on amd64-portbld-freebsd8.2, compiled by GCC cc 
> (GCC) 4.2.1 20070719  [FreeBSD], 64-bit
>
> The machines has 4GB of RAM with the following config:
> - shared_buffers: 512MB
> - effective_cache_size: 2GB
> - work_mem: 32MB
> - maintenance_work_mem: 128MB
> - default_statistics_target: 300
> - temp_buffers: 64MB
> - wal_buffers: 8MB
> - checkpoint_segments = 15
>
> The tables have been ANALYZE'd. I've put the EXPLAIN ANALYZE on:
>
> - http://www.pastie.org/3731956 : with default config
> - http://www.pastie.org/3731960 : this is with enable_seq_scan = off
> - http://www.pastie.org/3731962 : I tried to play on the various cost 
> settings but it's doesn't change anything, except setting 
> random_page_cost to 1 (which will lead to bad plans for other queries, 
> so not a solution)
> - http://www.pastie.org/3732035 : with enable_hashagg and 
> enable_hashjoin to false
>
> I'm currently out of idea why PostgreSQL still chooses a bad plan for 
> this query ... any hint :) ?
>
> Thank you,
> Julien
>
>
>


--------------000908030109070706000309
Content-Type: text/html; charset=UTF-8
Content-Transfer-Encoding: 8bit

<html>
  <head>
    <meta content="text/html; charset=UTF-8" http-equiv="Content-Type">
  </head>
  <body text="#000000" bgcolor="#FFFFFF">
    Julien,<br>
    <br>
    It looks like you forgot to post the query.<br>
    <br>
    Mario<br>
    On 2012-04-05 1:38 PM, Julien Cigar wrote:
    <blockquote cite="mid:4F7D843E.4060304@ulb.ac.be" type="cite">Hello,
      <br>
      <br>
      I have an extremely bad plan for one of my colleague's query.
      Basically PostgreSQL chooses to seq scan instead of index scan.
      This is on:
      <br>
      <br>
      antabif=# select version();
      <br>
                                                       version
      <br>
----------------------------------------------------------------------------------------------------------
      <br>
       PostgreSQL 9.0.7 on amd64-portbld-freebsd8.2, compiled by GCC cc
      (GCC) 4.2.1 20070719  [FreeBSD], 64-bit
      <br>
      <br>
      The machines has 4GB of RAM with the following config:
      <br>
      - shared_buffers: 512MB
      <br>
      - effective_cache_size: 2GB
      <br>
      - work_mem: 32MB
      <br>
      - maintenance_work_mem: 128MB
      <br>
      - default_statistics_target: 300
      <br>
      - temp_buffers: 64MB
      <br>
      - wal_buffers: 8MB
      <br>
      - checkpoint_segments = 15
      <br>
      <br>
      The tables have been ANALYZE'd. I've put the EXPLAIN ANALYZE on:
      <br>
      <br>
      - <a class="moz-txt-link-freetext" href="http://www.pastie.org/3731956">http://www.pastie.org/3731956</a> : with default config
      <br>
      - <a class="moz-txt-link-freetext" href="http://www.pastie.org/3731960">http://www.pastie.org/3731960</a> : this is with enable_seq_scan =
      off
      <br>
      - <a class="moz-txt-link-freetext" href="http://www.pastie.org/3731962">http://www.pastie.org/3731962</a> : I tried to play on the various
      cost settings but it's doesn't change anything, except setting
      random_page_cost to 1 (which will lead to bad plans for other
      queries, so not a solution)
      <br>
      - <a class="moz-txt-link-freetext" href="http://www.pastie.org/3732035">http://www.pastie.org/3732035</a> : with enable_hashagg and
      enable_hashjoin to false
      <br>
      <br>
      I'm currently out of idea why PostgreSQL still chooses a bad plan
      for this query ... any hint :) ?
      <br>
      <br>
      Thank you,
      <br>
      Julien
      <br>
      <br>
      <br>
      <fieldset class="mimeAttachmentHeader"></fieldset>
      <br>
      <pre wrap="">
</pre>
    </blockquote>
    <br>
  </body>
</html>

--------------000908030109070706000309--