From: Tom Lane <tgl@sss.pgh.pa.us>
To: Lincoln Swaine-Moore <lswainemoore@gmail.com>
cc: legrand legrand <legrand_legrand@hotmail.com>,
        pgsql-performance@postgresql.org
Subject: Re: Improving Performance of Query ~ Filter by A, Sort by B
In-reply-to: 
 <CABcidkLEZ=WifDQJLtPFaTeFLYTFkEbXDhbFLGVk5FzTVDSbbw@mail.gmail.com>
References: 
 <CABcidk+P9Ujq-qvXXjivGrYjm7z_zJUgpjJEM8hXhrxgraR8_Q@mail.gmail.com>
 <1531345315560-0.post@n3.nabble.com>
 <CABcidkLEZ=WifDQJLtPFaTeFLYTFkEbXDhbFLGVk5FzTVDSbbw@mail.gmail.com>
Comments: In-reply-to Lincoln Swaine-Moore <lswainemoore@gmail.com>
	message dated "Wed, 11 Jul 2018 19:31:46 -0400"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <23354.1531365018.1@sss.pgh.pa.us>
Content-Transfer-Encoding: quoted-printable
Date: Wed, 11 Jul 2018 23:10:18 -0400
Message-ID: <23355.1531365018@sss.pgh.pa.us>
Precedence: bulk

Lincoln Swaine-Moore <lswainemoore@gmail.com> writes:
> Here's the result (I turned off the timeout and got it to finish):
> ...

I think the core of the problem here is bad rowcount estimation.  We can't
tell from your output how many rows really match

> WHERE "a"."parent_id" IN (
>     49188,14816,14758,8402
> )

but the planner is guessing there are 5823 of them.  In the case with
only three IN items, we have

>          ->  Index Scan using a_parent_id_idx1 on a_partition1 a (cost=3D=
0.43..4888.37 rows=3D4367 width=3D12) (actual time=3D5.581..36.270 rows=3D=
50 loops=3D1)
>                Index Cond: (parent_id =3D ANY ('{19948,21436,41220}'::in=
teger[]))

so the planner thinks there are 4367 matching rows but there are only 50.
Anytime you've got a factor-of-100 estimation error, you're going to be
really lucky if you get a decent plan.

I suggest increasing the statistics target for the parent_id column
in hopes of getting better estimates for the number of matches.

			regards, tom lane