pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  

help / color / mirror / Atom feed
From: Andrew Gierth <andrew@tao11.riddles.org.uk>
To: MichaelDBA <MichaelDBA@sqlexec.com>
Cc: Jeff Janes <jeff.janes@gmail.com>
Cc: Justin Pryzby <pryzby@telsasoft.com>
Cc: Ivan Voras <ivoras@gmail.com>
Cc: postgres performance list <pgsql-performance@postgresql.org>
Subject: Re: Optimising a two column OR check
Date: Sat, 12 Oct 2019 17:58:22 +0100
Message-ID: <87imotx5uq.fsf@news-spur.riddles.org.uk> (raw)
In-Reply-To: <c1c41715-326d-3649-8b1d-918bfdbc9831@sqlexec.com>
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>

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

 MichaelDBA> Yep, you're right, Andrew, adding a couple rows made it do
 MichaelDBA> the index only scan.  I reckon I got misled by turning off
 MichaelDBA> sequential scans, thinking that actual rows were not
 MichaelDBA> important anymore.  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.

-- 
Andrew (irc:RhodiumToad)





view thread (14+ messages)  latest in thread

Message-ID: <87imotx5uq.fsf@news-spur.riddles.org.uk>
Permalink:  ../87imotx5uq.fsf@news-spur.riddles.org.uk/
Also on:    postgresql.org/message-id/87imotx5uq.fsf@news-spur.riddles.org.uk

 · 

reply
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: andrew@tao11.riddles.org.uk, MichaelDBA@sqlexec.com, jeff.janes@gmail.com, pryzby@telsasoft.com, ivoras@gmail.com
  Subject: Re: Optimising a two column OR check
  In-Reply-To: <87imotx5uq.fsf@news-spur.riddles.org.uk>

* 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