From: Justin Pryzby <pryzby@telsasoft.com>
To: singh400@gmail.com <singh400@gmail.com>
Cc: pgsql-performance@lists.postgresql.org, David Rowley <dgrowleyml@gmail.com>
Subject: Re: Duplicate WHERE condition changes performance and plan
Date: Sun, 19 Apr 2020 21:33:57 -0500
Message-ID: <20200420023357.GR26953@telsasoft.com> (raw)
In-Reply-To: <CAOtbvR+_tqYfHc1GtPkFsNkffXoecUusxDfj-FJ+o9-aOpZySg@mail.gmail.com> <CAOtbvRKSSg5VOY1tE7h4tM09J=etjNWyM8cMm=G-cRk7=KyeEw@mail.gmail.com>
On Wed, Apr 15, 2020 at 08:55:53PM +0100, singh400@gmail.com wrote:
> We have an odd issue where specifying the same where clause twice causes PG
> to pick a much more efficent plan. We would like to know why.
> Query B (this is the 'fast' query):
> UPDATE problem_instance SET processed = false
> FROM problem
> WHERE problem.id = problem_instance.problem_id
> AND problem.status != 2
> AND problem.status != 2
> AND processed = true;
When you specify redundant condition, it results in an underestimate, as
expected:
Index Scan using problem_id_idx1 on public.problem (cost=0.28..624.68 ROWS=73 width=14) (actual time=0.011..0.714 ROWS=841 loops=1)
Filter: ((problem.status <> 2) AND (problem.status <> 2))
In this case, doing an index scans on problem_instance is apparently faster
than an seq scan.
I think the duplicate condition is fooling the planner, and by chance it's
giving a better plan. That might indicate that your settings aren't ideal.
Maybe random_page_cost should be lower, which would encourage index scans. If
you're using SSD storage, or if the DB is small compared with shared_buffers or
RAM, then random_page_cost should be closer to seq_page_cost.
How large are the indexes? problem_id_idx1 ?
On Mon, Apr 20, 2020 at 01:50:17AM +0100, singh400@gmail.com wrote:
> Even after upgrading my local install of PG to "PostgreSQL 12.2,
> compiled by Visual C++ build 1914, 64-bit" and I'm still seeing the
> same behaviour.
> Server Configuration:
> https://gist.github.com/indy-singh/8386d59206af042d365e5cd49fbae68f
> shared_buffers 2GB configuration file
> effective_cache_size 6GB configuration file
Note, until v10, the documentation said this:
https://www.postgresql.org/docs/9.6/runtime-config-resource.html
|Also, on Windows, large values for shared_buffers aren't as effective. You may
|find better results keeping the setting relatively low and using the operating
|system cache more instead. The useful range for shared_buffers on Windows
|systems is generally from 64MB to 512MB.
It would be interesting to know
--
Justin
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: pryzby@telsasoft.com, singh400@gmail.com, dgrowleyml@gmail.com
Subject: Re: Duplicate WHERE condition changes performance and plan
In-Reply-To: <20200420023357.GR26953@telsasoft.com>
* 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