From: Andrew Gierth <andrew@tao11.riddles.org.uk>
To: MichaelDBA <MichaelDBA@sqlexec.com>
Cc: Jeff Janes <jeff.janes@gmail.com>,  Justin Pryzby <pryzby@telsasoft.com>,
  Ivan Voras <ivoras@gmail.com>,
  postgres performance list <pgsql-performance@postgresql.org>
Subject: Re: Optimising a two column OR check
In-Reply-To: <c1c41715-326d-3649-8b1d-918bfdbc9831@sqlexec.com>
	(MichaelDBA@sqlexec.com's message of "Sat, 12 Oct 2019 11:54:07
	-0400")
Message-ID: <87imotx5uq.fsf@news-spur.riddles.org.uk>
References: 
 <CAF-QHFXQduEoX=ADZh2QYECLdcM1zOuY6=K0cE_DBjhdH3v+WA@mail.gmail.com>
	<20191012144336.GA4475@telsasoft.com>
	<CAMkU=1w1F0t7c78mJMF7DdBbk50aGy3-AROGzcbdPrS0ApmGAA@mail.gmail.com>
	<a0ab3bae-6a5d-b2ea-b3a0-cbcff72f394f@sqlexec.com>
	<87y2xqvv1x.fsf@news-spur.riddles.org.uk>
	<25d9306a-d0e9-ed82-3128-bff469f09aed@sqlexec.com>
	<87tv8evuq0.fsf@news-spur.riddles.org.uk>
	<c1c41715-326d-3649-8b1d-918bfdbc9831@sqlexec.com>
User-Agent: Gnus/5.13 (Gnus v5.13) Emacs/26.2 (berkeley-unix)
Date: Sat, 12 Oct 2019 17:58:22 +0100
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: quoted-printable
Precedence: bulk

>>>>> "MichaelDBA" =3D=3D MichaelDBA  <MichaelDBA@sqlexec.com> writes:

 MichaelDBA> Yep, you're right, Andrew, adding a couple rows made it do
 MichaelDBA> the index only scan.=C2=A0 I reckon I got misled by turning off
 MichaelDBA> sequential scans, thinking that actual rows were not
 MichaelDBA> important anymore.=C2=A0 Overly simplistic reasonings can get
 MichaelDBA> one into trouble, lol.

We do some odd stuff with the statistics estimates for completely empty
tables because (a) it's not common in practice for a table to be always
empty (i.e. the emptiness is usually transient) and (b) if you take the
emptiness of a table at face value, you end up generating insanely bad
plans for certain FK check queries that may not get replanned quickly
enough to mitigate the performance impact.

--=20
Andrew (irc:RhodiumToad)