From: pinker <pinker@onet.eu>
To: pgsql-performance@postgresql.org
Subject: Re: Table performance with millions of rows (partitioning)
Date: Wed, 27 Dec 2017 19:22:14 -0700 (MST)
Message-ID: <1514427734055-0.post@n3.nabble.com> (raw)
In-Reply-To: <47DA908F-BC21-45E6-8E30-73AFAC5F4853@inoc.net>
References: <7DF18AB9-C4A4-4C28-957D-12C00FCB5F71@inoc.net>
<20171228012009.GI4172@telsasoft.com>
<47DA908F-BC21-45E6-8E30-73AFAC5F4853@inoc.net>
No, it's unfortunately not possible.
Documentation says in Caveats part:
/Constraint exclusion only works when the query's WHERE clause contains
constants (or externally supplied parameters). For example, a comparison
against a non-immutable function such as CURRENT_TIMESTAMP cannot be
optimized, since the planner cannot know which partition the function value
might fall into at run time.
Keep the partitioning constraints simple, else the planner may not be able
to prove that partitions don't need to be visited. Use simple equality
conditions for list partitioning, or simple range tests for range
partitioning, as illustrated in the preceding examples. A good rule of thumb
is that partitioning constraints should contain only comparisons of the
partitioning column(s) to constants using B-tree-indexable operators./
Even making a function in SQL or plpgsql and declaring it as immutable will
not help. Postgres will always check against all the partitions. It's not
enough "simple" for the planner.
--
Sent from: http://www.postgresql-archive.org/PostgreSQL-performance-f2050081.html
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: pinker@onet.eu
Subject: Re: Table performance with millions of rows (partitioning)
In-Reply-To: <1514427734055-0.post@n3.nabble.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