Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1dsUAw-0005hE-3C for pgsql-performance@arkaria.postgresql.org; Thu, 14 Sep 2017 13:27:26 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84_2) (envelope-from ) id 1dsUAv-0004ie-MC for pgsql-performance@arkaria.postgresql.org; Thu, 14 Sep 2017 13:27:25 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84_2) (envelope-from ) id 1dsU97-0001C4-8A for pgsql-performance@postgresql.org; Thu, 14 Sep 2017 13:25:33 +0000 Received: from mail-io0-x22b.google.com ([2607:f8b0:4001:c06::22b]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.84_2) (envelope-from ) id 1dsU8y-0008LC-W4 for pgsql-performance@postgresql.org; Thu, 14 Sep 2017 13:25:32 +0000 Received: by mail-io0-x22b.google.com with SMTP id w94so332799ioi.7 for ; Thu, 14 Sep 2017 06:25:24 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20161025; h=mime-version:in-reply-to:references:from:date:message-id:subject:to :cc; bh=F6mX/U3mJWma2lVXGP6Mg6SK7o9953vScts3oQXjmgc=; b=ohFFYQn1CBM/SnK4fCTSpckx6n9g5cC1IKmNU0qebYkZuT9QQ/gQakgTlIILPDleIa 1/VnbCO9A315trnZ+fBc5YUcuTJHGckZ5pGAIwdIYxSgQNcvwLE5fuXmVJktiU/WVxIg HW8s1JrEw7hNBtRriRWUTv6FSdqhh5AP2F4Gbn1vUCxcLMf0rbHr0H2skypjjZr3uldn 0PFjM946HreTAkW1XL0MIew4lzY710FyUyMCnCCo3uBYHSrjyW6qOokpOTuov2qFGvrH uHt5ErNqnnelKwLo41jMIFtM9roztPGIz08MkC9r+LZBOOELqCg4Ol8zjuJ0QFmeuJuV i5wg== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20161025; h=x-gm-message-state:mime-version:in-reply-to:references:from:date :message-id:subject:to:cc; bh=F6mX/U3mJWma2lVXGP6Mg6SK7o9953vScts3oQXjmgc=; b=EmvrXMS3Ow+i83VEoij4cqNrHLNqIX+PhmqeYNkf1ivkBMFUxlhQzf9RilnjgDuIVN n/u0NiNFaGwTPfJHEm9kvZrYn1ecdfwT/SQTT67YKB2en7CXE4JyLMqi5mE/+neHGUYZ /KMSXyHpxYLM1GrZ8M+hHmC2P6cclGd4rU6CQqWZeVhMNn0tH+sg321scGMdi7RT09Rs hWpvdYdoHyf/CINpg17kDcf3WwVJ8Pf3EujUw4KyJ1ZBYZWmk2kVhx0sLe7EPJGyYBp2 mc6uF/S2vxsTXqOB2oiYnrwvxg1LZC3AVvQaJPWciW6okL7R2vRDUGGwaWEsCp+hJHZH VYug== X-Gm-Message-State: AHPjjUiH1MnwklvGbUt9eQgzseeHmEHkqWNpJ6ZUHjZG7U3tgia87VQ2 RJ3vDJbp6j9EF7rpDSi5IW8P/g5M+I/l9/XSpVE= X-Google-Smtp-Source: AOwi7QClm5/3Ak8zcUugzOPuGSB0IBT1E/qgNAAtkf5a0tTMVzNqluH9d3TxEQXEt38W3giULkS+kYPg0e/ri4JzOko= X-Received: by 10.202.4.15 with SMTP id 15mr20384540oie.125.1505395523038; Thu, 14 Sep 2017 06:25:23 -0700 (PDT) MIME-Version: 1.0 Received: by 10.157.1.235 with HTTP; Thu, 14 Sep 2017 06:25:22 -0700 (PDT) In-Reply-To: References: From: Mike Broers Date: Thu, 14 Sep 2017 08:25:22 -0500 Message-ID: Subject: Re: query of partitioned object doesnt use index in qa To: David Rowley Cc: postgres performance list Content-Type: multipart/alternative; boundary="001a113c0faa65cc8c0559263589" List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-performance Precedence: bulk Sender: pgsql-performance-owner@postgresql.org --001a113c0faa65cc8c0559263589 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable Thanks for the suggestions, I'll futz with random_page_cost and effective_cache_size a bit and follow up, as well as try to provide an explain analyze on both (if the longer query ever returns!) Most appreciated. On Wed, Sep 13, 2017 at 4:57 PM, David Rowley wrote: > On 14 September 2017 at 08:28, Mike Broers wrote: > > I have a query of a partitioned table that uses the partition index in > > production but uses sequence scans in qa. The only major difference I > can > > tell is the partitions are much smaller in qa. In production the > partitions > > range in size from around 25 million rows to around 60 million rows, in > QA > > the partitions are between 4 and 12 million rows. I would think this > would > > be big enough to get the planner to prefer the index but this is the > major > > difference between the two database as far as I can tell. > > > QA: > > > =E2=94=82 -> Seq Scan on event__99999999 e_1 > > (cost=3D0.00..2527918.06 rows=3D11457484 width=3D782) =E2=94= =82 > > > > Production: > > > > =E2=94=82 -> Index Scan using > > ix_event__00011162_landing_id on event__00011162 e_1 > (cost=3D0.56..15476.59 > > rows=3D23400 width=3D572) =E2=94=82 > > > If QA has between 4 and 12 million rows, then the planner's row > estimate for the condition thinks 11457484 are going to match, so a > Seqscan is likely best here. If those estimates are off then it might > be worth double checking your nightly analyze is working correctly on > QA. > > The planner may be able to be coaxed into using the index with a > higher effective_cache_size and/or a lower random_page_cost setting, > although you really should be looking at those row estimates first. > Showing us the EXPLAIN ANALYZE would have been much more useful so > that we could have seen if those are accurate or not. > > -- > David Rowley http://www.2ndQuadrant.com/ > PostgreSQL Development, 24x7 Support, Training & Services > --001a113c0faa65cc8c0559263589 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
Thanks for the suggestions, I'll futz with random_page= _cost =C2=A0and effective_cache_size a bit and follow up, as well as try to= provide an explain analyze on both (if the longer query ever returns!)
Most appreciated.
On Wed, Sep 13, 2017 at 4:57 PM, David Rowley <= span dir=3D"ltr"><david.rowley@2ndquadrant.com> wrote:
On 14 September 2017 at 08:28, Mike = Broers <mbroers@gmail.com> w= rote:
> I have a query of a partitioned table that uses the partition index in=
> production but uses sequence scans in qa.=C2=A0 The only major differe= nce I can
> tell is the partitions are much smaller in qa.=C2=A0 In production the= partitions
> range in size from around 25 million rows to around 60 million rows, i= n QA
> the partitions are between 4 and 12 million rows.=C2=A0 I would think = this would
> be big enough to get the planner to prefer the index but this is the m= ajor
> difference between the two database as far as I can tell.


QA:

> =E2=94=82=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0->=C2=A0 Seq Scan on event__999999= 99 e_1
> (cost=3D0.00..2527918.06 rows=3D11457484 width=3D782)=C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =E2=94=82
>

Production:
>
> =E2=94=82=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0->=C2=A0 Index Scan using
> ix_event__00011162_landing_id on event__00011162 e_1=C2=A0 (cost=3D0.5= 6..15476.59
> rows=3D23400 width=3D572)=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0=E2=94=82


If QA has between 4 and 12 million rows, then the planner's row<= br> estimate for the condition thinks 11457484 are going to match, so a
Seqscan is likely best here. If those estimates are off then it might
be worth double checking your nightly analyze is working correctly on
QA.

The planner may be able to be coaxed into using the index with a
higher effective_cache_size and/or a lower random_page_cost setting,
although you really should be looking at those row estimates first.
Showing us the EXPLAIN ANALYZE would have been much more useful so
that we could have seen if those are accurate or not.

--
=C2=A0David Rowley=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0http://www.2ndQuadrant.com/
=C2=A0PostgreSQL Development, 24x7 Support, Training & Services

--001a113c0faa65cc8c0559263589--