From: Davor J. <DavorJ@live.com>
To: pgsql-performance@postgresql.org
Subject: queries with subquery constraints on partitioned tables not optimized?
Date: Tue, 2 Feb 2010 10:48:08 +0100
Message-ID: <hk8sd1$2b79$1@news.hub.org> (raw)
Let's say you have one partitioned table, "tbl_p", partitioned according to
the PK "p_pk". I have made something similar with triggers, basing myself on
the manual for making partitioned tables.
According to the manual, optimizer searches the CHECKs of the partitions to
determine which table(s) to use (if applicable).
So if one has CHECKs of kind "p_pk = some number", queries like "SELECT *
from tbl_p where p_pk = 1" will only be searched in the appropriate table.
One can check this with EXPLAIN. So far so good.
Now, if one takes a subquery for "1", the optimizer evaluates it first
(let's say to "1"), but then searches for it (sequentially) in every
partition, which, for large partitions, can be very time-consuming and goes
beyond the point of partitioning.
Is this normal, or am I missing something?
Kind regards,
Davor
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: DavorJ@live.com
Subject: Re: queries with subquery constraints on partitioned tables not optimized?
In-Reply-To: <hk8sd1$2b79$1@news.hub.org>
* 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