Received: from malur.postgresql.org ([217.196.149.56])
 by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from
 <pgsql-performance-owner+M50262=pgsql-performance-ar@postgresql.org>)
 id 1V5It4-0003s4-BR
 for pgsql-performance@arkaria.postgresql.org; Fri, 02 Aug 2013 17:11:34 +0000
Received: from localhost ([127.0.0.1] helo=postgresql.org)
 by malur.postgresql.org with smtp (Exim 4.80) (envelope-from
 <pgsql-performance-owner+M50262=pgsql-performance-ar@postgresql.org>)
 id 1V5It3-0005RR-Ig
 for pgsql-performance@arkaria.postgresql.org; Fri, 02 Aug 2013 17:11:33 +0000
Received: from makus.postgresql.org ([2001:4800:7903:4::125])
 by malur.postgresql.org with esmtp (Exim 4.80)
 (envelope-from <eshkinkot@gmail.com>) id 1V5It2-0005RL-Oa
 for pgsql-performance@postgresql.org; Fri, 02 Aug 2013 17:11:32 +0000
Received: from mail-lb0-x230.google.com ([2a00:1450:4010:c04::230])
 by makus.postgresql.org with esmtp (Exim 4.80)
 (envelope-from <eshkinkot@gmail.com>) id 1V5Isz-0006jF-IJ
 for pgsql-performance@postgresql.org; Fri, 02 Aug 2013 17:11:32 +0000
Received: by mail-lb0-f176.google.com with SMTP id w10so615009lbi.21
 for <pgsql-performance@postgresql.org>; Fri, 02 Aug 2013 10:11:27 -0700 (PDT)
DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20120113;
 h=from:to:cc:subject:references:date:in-reply-to:message-id
 :user-agent:mime-version:content-type;
 bh=CZWOC96XebiOApuRf5tPa8cPTrwEWHSd+jO11zL5yX0=;
 b=grwtVif+nMZPwodQFcCd9rUStr1P/1n79UEAUltDesg6PlkXyEZCUV7H1RExOZJNdb
 a46d1ydJU8eDLO4Vg6uZnJIMJb+MZZkAl23y5Hj+EaqJdJlOyG2d/51IT1iodrL+LnDR
 //XCfHveWPHmXO9bgIzVKlVmOu2IWmEhoPWhawAEcBsOqG0K6iC1NImr5hPs6ivmbsxk
 1B+omc7aVlGyyNHgkRfxa92+gDLYCvASPWB1SDUfFU5SEz3EdsL4JwQCyQ4jSvCrNBzf
 zOaK9IdG/VjkUEAw5Ax0h6Vrfc39ixgnqAm2AhGl3vS0R6D/sL8k38nbzd3jGMechRrl
 ZAZw==
X-Received: by 10.152.29.103 with SMTP id j7mr3400688lah.7.1375463487188;
 Fri, 02 Aug 2013 10:11:27 -0700 (PDT)
Received: from seb.koffice.internal (koffice.avito.ru. [195.151.206.25])
 by mx.google.com with ESMTPSA id js17sm3719941lab.5.2013.08.02.10.11.25
 for <multiple recipients>
 (version=TLSv1.2 cipher=RC4-SHA bits=128/128);
 Fri, 02 Aug 2013 10:11:26 -0700 (PDT)
From: Sergey Burladyan <eshkinkot@gmail.com>
To: Jeff Janes <jeff.janes@gmail.com>
Cc: Alvaro Herrera <alvherre@2ndquadrant.com>,
 "pgsql-performance\@postgresql.org" <pgsql-performance@postgresql.org>
Subject: Re: Looks like merge join planning time is too big, 55 seconds
References: 
 <CAJ2ymdjK-+LZHFY+cskRq98c_4F1PEfveOVNbNGpWL0Pczy5Ew@mail.gmail.com>
 <8761vpwid4.fsf@seb.koffice.internal>
 <CAMkU=1zyWZEC0YfQ0D4obWWHZqcLoLApUzf-f2+LOVH2ib=s_g@mail.gmail.com>
 <87li4lusvd.fsf@seb.koffice.internal>
 <CAMkU=1zuaFU7-DBJGawX+P+e4HjXmL+R_sxOLoB4Q18B5fRPHw@mail.gmail.com>
 <87li4l9j34.fsf@home.progtech.ru> <87k3k5kkup.fsf@home.progtech.ru>
 <20130802031914.GE5669@eldon.alvh.no-ip.org>
 <87haf8v2h8.fsf@seb.koffice.internal>
 <CAMkU=1yZvKht41LTrQB5UO7J=bO+beFLDLZ5E84_nRNLj20eRQ@mail.gmail.com>
Date: Fri, 02 Aug 2013 21:11:24 +0400
In-Reply-To: 
 <CAMkU=1yZvKht41LTrQB5UO7J=bO+beFLDLZ5E84_nRNLj20eRQ@mail.gmail.com>
 (Jeff Janes's message of "Fri, 2 Aug 2013 08:35:45 -0700")
Message-ID: <874nb8uif7.fsf@seb.koffice.internal>
User-Agent: Gnus/5.13 (Gnus v5.13) Emacs/24.3 (gnu/linux)
MIME-Version: 1.0
Content-Type: text/plain
X-Pg-Spam-Score: -2.0 (--)
List-Archive: <http://archives.postgresql.org/pgsql-performance>
List-Help: <mailto:majordomo@postgresql.org?body=help>
List-ID: <pgsql-performance.postgresql.org>
List-Owner: <mailto:pgsql-performance-owner@postgresql.org>
List-Post: <mailto:pgsql-performance@postgresql.org>
List-Subscribe: <mailto:majordomo@postgresql.org?body=sub%20pgsql-performance>
List-Unsubscribe: 
 <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
X-Mailing-List: pgsql-performance
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org

Jeff Janes <jeff.janes@gmail.com> writes:

> On Fri, Aug 2, 2013 at 2:58 AM, Sergey Burladyan <eshkinkot@gmail.com> wrote:
> >
> > PS: I think my main problem is here:
> > select min(user_id) from items;
> >  min
> > -----
> >    1
> > (1 row)
> >
> > Time: 504.520 ms
>
> That is a long time, but still 100 fold less than the planner is taking.
>
> What about max(user_id)?

max is good, only rows with user_id = 0 was updated:

select max(user_id) from items;
Time: 59.646 ms

> > also, i cannot reindex it concurrently now, because it run autovacuum: VACUUM ANALYZE public.items (to prevent wraparound)
>
> That is going to take a long time if you have the cost settings at
> their defaults.

Yes, I have custom setting, more slow, it will last about a week.

> But why is it bloated in this way? 

Don't known. It has been updated many items last week. ~ 10% of table.

> It must be visiting many thousands of dead/invisible rows before
> finding the first visible one.  But, Btree index have a mechanism to
> remove dead tuples from indexes, so it doesn't follow them over and
> over again (see "kill_prior_tuple").  So is that mechanism not
> working, or are the tuples not dead but just invisible (i.e. inserted
> by a still open transaction)?

It is deleted, but VACUUM still not completed.

BTW, it is standby server, and it query plan (block read) is very
different from master:

Hot standby:

explain (analyze,verbose,buffers) select min(user_id) from items;

'Result  (cost=0.12..0.13 rows=1 width=0) (actual time=56064.514..56064.514 rows=1 loops=1)'
'  Output: $0'
'  Buffers: shared hit=3694164 read=6591224 written=121652'
'  InitPlan 1 (returns $0)'
'    ->  Limit  (cost=0.00..0.12 rows=1 width=8) (actual time=56064.502..56064.503 rows=1 loops=1)'
'          Output: public.items.user_id'
'          Buffers: shared hit=3694164 read=6591224 written=121652'
'          ->  Index Only Scan using items_user_id_idx on public.items  (cost=0.00..24165743.48 rows=200673143 width=8) (actual time=56064.499..56064.499 rows=1 loops=1)'
'                Output: public.items.user_id'
'                Index Cond: (public.items.user_id IS NOT NULL)'
'                Heap Fetches: 8256426'
'                Buffers: shared hit=3694164 read=6591224 written=121652'
'Total runtime: 56064.571 ms'

Master:

'Result  (cost=0.12..0.13 rows=1 width=0) (actual time=202.759..202.759 rows=1 loops=1)'
'  Output: $0'
'  Buffers: shared hit=153577 read=1'
'  InitPlan 1 (returns $0)'
'    ->  Limit  (cost=0.00..0.12 rows=1 width=8) (actual time=202.756..202.757 rows=1 loops=1)'
'          Output: public.items.user_id'
'          Buffers: shared hit=153577 read=1'
'          ->  Index Only Scan using items_user_id_idx on public.items  (cost=0.00..24166856.02 rows=200680528 width=8) (actual time=202.756..202.756 rows=1 loops=1)'
'                Output: public.items.user_id'
'                Index Cond: (public.items.user_id IS NOT NULL)'
'                Heap Fetches: 0'
'                Buffers: shared hit=153577 read=1'
'Total runtime: 202.786 ms'

And from backup, before index|heap bloated :)

 Result  (cost=0.87..0.88 rows=1 width=0) (actual time=16.002..16.003 rows=1 loops=1)
   Output: $0
   Buffers: shared hit=3 read=4
   InitPlan 1 (returns $0)
     ->  Limit  (cost=0.00..0.87 rows=1 width=8) (actual time=15.993..15.995 rows=1 loops=1)
           Output: public.items.user_id
           Buffers: shared hit=3 read=4
           ->  Index Only Scan using items_user_id_idx on public.items  (cost=0.00..169143085.72 rows=193309210 width=8) (actual time=15.987..15.987 rows=1 loops=1)
                 Output: public.items.user_id
                 Index Cond: (public.items.user_id IS NOT NULL)
                 Heap Fetches: 1
                 Buffers: shared hit=3 read=4
 Total runtime: 16.057 ms


-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance