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

help / color / mirror / Atom feed
From: Tom Lane <tgl@sss.pgh.pa.us>
To: Teja Jakkidi <teja.jakkidi05@gmail.com>
Cc: Doug Reynolds <mav@wastegate.net>
Cc: gogala.mladen@gmail.com, pgsql-admin <pgsql-admin@lists.postgresql.org>
Subject: Re: Performance issue after creating partitions
Date: Tue, 30 Aug 2022 17:40:37 -0400
Message-ID: <3757070.1661895637@sss.pgh.pa.us> (raw)
In-Reply-To: <6F2BE85B-F6D0-4D4C-9DEA-EEE09D97B8EF@gmail.com>
References: <738BC8F9-5F1E-4E8F-8EB9-DEE6EDAA740B@gmail.com>
	<6F2BE85B-F6D0-4D4C-9DEA-EEE09D97B8EF@gmail.com>

Teja Jakkidi <teja.jakkidi05@gmail.com> writes:
> Anyone ever encountered the below situation as what I am noticing with partitions?

You haven't shown us your query, so any answer would be blind speculation.

However, in the spirit of blind speculation, I'm wondering if you're
expecting those range constraints to propagate across a join.  They
won't; you'd need to duplicate the conditions for the other table.

That is, if you have WHERE+JOIN/ON conditions amounting to

	WHERE a.a = b.b AND b.b = constant

the planner is able to derive "a.a = constant" on the assumption of
transitivity, and use that to constrain the scan of table a (ie,
use an index, reject partitions at plan time, etc).  But no such
deduction happens for

	WHERE a.a = b.b AND b.b >= constant

If you want a constrained scan of a, you need to write it out:

	WHERE a.a = b.b AND b.b >= constant AND a.a >= constant

			regards, tom lane





view thread (8+ messages)  latest in thread

Message-ID: <3757070.1661895637@sss.pgh.pa.us>
Permalink:  ../3757070.1661895637@sss.pgh.pa.us/
Also on:    postgresql.org/message-id/3757070.1661895637@sss.pgh.pa.us

 · 

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-admin@postgresql.org
  Cc: tgl@sss.pgh.pa.us, teja.jakkidi05@gmail.com, mav@wastegate.net, pgsql-admin@lists.postgresql.org
  Subject: Re: Performance issue after creating partitions
  In-Reply-To: <3757070.1661895637@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