From: PG Doc comments form <noreply@postgresql.org>
To: pgsql-docs@lists.postgresql.org
Cc: niels@thinkiq.com
Subject: ORDER BY LIMIT n does not work as described when using partitioned tables
Date: Wed, 20 Mar 2019 04:40:18 +0000
Message-ID: <155305681875.30367.2600813692413489939@wrigleys.postgresql.org> (raw)
The following documentation comment has been logged on the website:
Page: https://www.postgresql.org/docs/10/indexes-ordering.html
Description:
Dear Postgres team,
First, thanks for an excellent product and great documentation. This is
world class.
We have found an issue with index and ORDER BY LIMIT 1 when using
partitioned tables. The tabled is partitioned using declarative
partitioning, the partition is created on timestamptz. There is one
partition per day.
Looking at the query planner, it is clear that it is trying to open up all
the indexes (we have hundreds of partitioned tables) to find the 1 row that
we are asking for, even if the row is in the first table that the index
should look at.
The result is that the query is extremely slow. All the benefits of using
partitioned tables seems to be gone, it is worse than not partitioning.
I would prefer that the issue is solved in the partitioned table, in this
case the documentation is correct. If it is not possible to solve the
partitioning issue, then the documentation should be updated.
Here are the scripts:
CREATE TABLE history.history_bools
(
thing_id bigint,
boolvalue boolean,
"timestamp" timestamp with time zone NOT NULL
) PARTITION BY RANGE ("timestamp")
WITH (
OIDS = FALSE
)
TABLESPACE pg_default;
ALTER TABLE history.history_bools
OWNER to postgres;
-- Partitions SQL
CREATE TABLE history.history_bools_2018_05_01 PARTITION OF
history.history_bools
FOR VALUES FROM ('2018-04-30 17:00:00-07') TO ('2018-05-01
17:00:00-07');
CREATE UNIQUE INDEX history_bools_2018_05_01_idx
ON history.history_bools_2018_05_01 USING btree
(thing_id, "timestamp")
TABLESPACE pg_default;
CREATE TABLE history.history_bools_2018_05_02 PARTITION OF
history.history_bools
FOR VALUES FROM ('2018-05-01 17:00:00-07') TO ('2018-05-02
17:00:00-07');
CREATE UNIQUE INDEX history_bools_2018_05_02_idx
ON history.history_bools_2018_05_02 USING btree
(thing_id, "timestamp")
TABLESPACE pg_default;
....
SELECT * FROM history.history_bools WHERE thing_id = 1 AND "timestamp" <
'2018-05-03 12:00:00';
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-docs@postgresql.org
Cc: noreply@postgresql.org, pgsql-docs@lists.postgresql.org, niels@thinkiq.com
Subject: Re: ORDER BY LIMIT n does not work as described when using partitioned tables
In-Reply-To: <155305681875.30367.2600813692413489939@wrigleys.postgresql.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