From: Tom Lane <tgl@sss.pgh.pa.us>
To: Lincoln Swaine-Moore <lswainemoore@gmail.com>
Cc: legrand legrand <legrand_legrand@hotmail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Improving Performance of Query ~ Filter by A, Sort by B
Date: Wed, 11 Jul 2018 23:10:18 -0400
Message-ID: <23355.1531365018@sss.pgh.pa.us> (raw)
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>
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=0.43..4888.37 rows=4367 width=12) (actual time=5.581..36.270 rows=50 loops=1)
> Index Cond: (parent_id = ANY ('{19948,21436,41220}'::integer[]))
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: tgl@sss.pgh.pa.us, lswainemoore@gmail.com, legrand_legrand@hotmail.com
Subject: Re: Improving Performance of Query ~ Filter by A, Sort by B
In-Reply-To: <23355.1531365018@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox