pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedLIMIT and UNION ALL
5+ messages / 4 participants
[nested] [flat]
* LIMIT and UNION ALL
@ 2011-05-18 15:26 Dave Johansen <davejohansen@gmail.com>
0 siblings, 2 replies; 5+ messages in thread
From: Dave Johansen @ 2011-05-18 15:26 UTC (permalink / raw)
To: pgsql-performance
I am using Postgres 8.3.3 and I have a VIEW which is a UNION ALL of two
tables but when I do a select on the view using a LIMIT, it scans the entire
tables and takes significantly longer than writing out the query with the
LIMITs in the sub-queries themselves. Is there a solution to get the view to
perform like the query with the LIMIT explicitly placed in the sub-queries?
I noticed a similar question in this post
http://archives.postgresql.org/pgsql-general/2005-06/msg00488.php but I
wasn't able to find an answer.
Thanks,
Dave
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: LIMIT and UNION ALL
@ 2011-05-18 15:54 Robert Klemme <shortcutter@googlemail.com>
parent: Dave Johansen <davejohansen@gmail.com>
1 sibling, 1 reply; 5+ messages in thread
From: Robert Klemme @ 2011-05-18 15:54 UTC (permalink / raw)
To: Dave Johansen <davejohansen@gmail.com>; +Cc: pgsql-performance
On Wed, May 18, 2011 at 5:26 PM, Dave Johansen <davejohansen@gmail.com> wrote:
> I am using Postgres 8.3.3 and I have a VIEW which is a UNION ALL of two
> tables but when I do a select on the view using a LIMIT, it scans the entire
> tables and takes significantly longer than writing out the query with the
> LIMITs in the sub-queries themselves. Is there a solution to get the view to
> perform like the query with the LIMIT explicitly placed in the sub-queries?
Can you show DDL and queries?
The query with the LIMIT on the subqueries and the one with the LIMIT
on the overall query are not semantically equivalent. Since you can
have an ORDER BY before the LIMIT on the query with the limit on the
view the database must have all the rows before it can apply the
ordering and properly determine the limit. Although it might be
possible to determine under particular circumstances that only one of
the tables needs to be queried or tables need only be queried
partially I deem that quite complex. I do not know whether Postgres
can do such optimizations but for that we would certainly need to see
the concrete example (including constraint and indexes).
Kind regards
robert
--
remember.guy do |as, often| as.you_can - without end
http://blog.rubybestpractices.com/
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: LIMIT and UNION ALL
@ 2011-05-18 15:59 Pavel Stehule <pavel.stehule@gmail.com>
parent: Dave Johansen <davejohansen@gmail.com>
1 sibling, 0 replies; 5+ messages in thread
From: Pavel Stehule @ 2011-05-18 15:59 UTC (permalink / raw)
To: Dave Johansen <davejohansen@gmail.com>; +Cc: pgsql-performance
Hello
2011/5/18 Dave Johansen <davejohansen@gmail.com>:
> I am using Postgres 8.3.3 and I have a VIEW which is a UNION ALL of two
> tables but when I do a select on the view using a LIMIT, it scans the entire
> tables and takes significantly longer than writing out the query with the
> LIMITs in the sub-queries themselves. Is there a solution to get the view to
> perform like the query with the LIMIT explicitly placed in the sub-queries?
>
> I noticed a similar question in this post
> http://archives.postgresql.org/pgsql-general/2005-06/msg00488.php but I
> wasn't able to find an answer.
maybe
SELECT *
FROM (SELECT * FROM tab1 LIMIT n) s1
UNION ALL
SELECT *
FROM (SELECT * FROM tab2 LIMIT n) s2
LIMIT n
Regards
Pavel Stehule
>
> Thanks,
> Dave
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: LIMIT and UNION ALL
@ 2011-05-26 16:21 Dave Johansen <davejohansen@gmail.com>
parent: Robert Klemme <shortcutter@googlemail.com>
0 siblings, 1 reply; 5+ messages in thread
From: Dave Johansen @ 2011-05-26 16:21 UTC (permalink / raw)
To: pgsql-performance
On Wed, May 18, 2011 at 8:54 AM, Robert Klemme
<shortcutter@googlemail.com>wrote:
> On Wed, May 18, 2011 at 5:26 PM, Dave Johansen <davejohansen@gmail.com>
> wrote:
> > I am using Postgres 8.3.3 and I have a VIEW which is a UNION ALL of two
> > tables but when I do a select on the view using a LIMIT, it scans the
> entire
> > tables and takes significantly longer than writing out the query with the
> > LIMITs in the sub-queries themselves. Is there a solution to get the view
> to
> > perform like the query with the LIMIT explicitly placed in the
> sub-queries?
>
> Can you show DDL and queries?
>
> The query with the LIMIT on the subqueries and the one with the LIMIT
> on the overall query are not semantically equivalent. Since you can
> have an ORDER BY before the LIMIT on the query with the limit on the
> view the database must have all the rows before it can apply the
> ordering and properly determine the limit. Although it might be
> possible to determine under particular circumstances that only one of
> the tables needs to be queried or tables need only be queried
> partially I deem that quite complex. I do not know whether Postgres
> can do such optimizations but for that we would certainly need to see
> the concrete example (including constraint and indexes).
>
> Kind regards
>
> robert
>
> --
> remember.guy do |as, often| as.you_can - without end
> http://blog.rubybestpractices.com/
>
Yes, there is an order by an index involved. Here's a simplified version of
the schema and queries that demonstrates the same behaviour.
Table "public.message1"
Column | Type |
Modifiers
--------+--------------------------+--------------------------------------------------------
rid | integer | not null default
nextval('message1_rid_seq'::regclass)
data | integer |
tlocal | timestamp with time zone |
Indexes:
"message1_pkey" PRIMARY KEY, btree (rid)
Referenced by:
TABLE "parsed1" CONSTRAINT "parsed1_msgid_fkey" FOREIGN KEY (msgid)
REFERENCES message1(rid)
Table "public.parsed1"
Column | Type |
Modifiers
--------+--------------------------+--------------------------------------------------------
rid | integer | not null default
nextval('parsed1_rid_seq'::regclass)
msgid | integer |
data | integer |
tlocal | timestamp with time zone |
Indexes:
"parsed1_pkey" PRIMARY KEY, btree (rid)
Foreign-key constraints:
"parsed1_msgid_fkey" FOREIGN KEY (msgid) REFERENCES message1(rid) ON
DELETE CASCADE
For this example, message2 has the same structure/definition and message1
and parsed2 has the same structure/definition as parsed1.
View "public.parsed_all"
Column | Type | Modifiers
--------+--------------------------+-----------
rid | integer |
msgid | integer |
data | integer |
tlocal | timestamp with time zone |
View definition:
SELECT parsed1.rid, parsed1.msgid, parsed1.data, parsed1.tlocal
FROM parsed1
UNION ALL
SELECT parsed2.rid, parsed2.msgid, parsed2.data, parsed2.tlocal
FROM parsed2;
Slow version using the view:
EXPLAIN ANALYZE SELECT * FROM parsed_all ORDER BY tlocal DESC LIMIT 10;
QUERY
PLAN
--------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=74985.28..74985.31 rows=10 width=20) (actual
time=6224.229..6224.244 rows=10 loops=1)
-> Sort (cost=74985.28..79985.28 rows=2000000 width=20) (actual
time=6224.226..6224.230 rows=10 loops=1)
Sort Key: parsed1.tlocal
Sort Method: top-N heapsort Memory: 17kB
-> Result (cost=0.00..31766.00 rows=2000000 width=20) (actual
time=0.026..4933.210 rows=2000000 loops=1)
-> Append (cost=0.00..31766.00 rows=2000000 width=20)
(actual time=0.024..2880.868 rows=2000000 loops=1)
-> Seq Scan on parsed1 (cost=0.00..15883.00
rows=1000000 width=20) (actual time=0.023..551.870 rows=1000000 loops=1)
-> Seq Scan on parsed2 (cost=0.00..15883.00
rows=1000000 width=20) (actual time=0.027..549.465 rows=1000000 loops=1)
Total runtime: 6224.337 ms
(9 rows)
Fast version using a direct query with limits in the sub-queries:
EXPLAIN ANALYZE SELECT * FROM (SELECT * FROM (SELECT * FROM parsed1 ORDER BY
tlocal DESC LIMIT 10) AS a UNION ALL SELECT * FROM (SELECT * FROM parsed2
ORDER BY tlocal DESC LIMIT 10) AS b) AS c ORDER BY tlocal DESC LIMIT 10;
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------
---------------------
Limit (cost=1.33..1.35 rows=10 width=20) (actual time=0.131..0.145 rows=10
loops=1)
-> Sort (cost=1.33..1.38 rows=20 width=20) (actual time=0.129..0.132
rows=10 loops=1)
Sort Key: parsed1.tlocal
Sort Method: quicksort Memory: 17kB
-> Result (cost=0.00..0.90 rows=20 width=20) (actual
time=0.023..0.100 rows=20 loops=1)
-> Append (cost=0.00..0.90 rows=20 width=20) (actual
time=0.020..0.078 rows=20 loops=1)
-> Limit (cost=0.00..0.35 rows=10 width=20) (actual
time=0.020..0.035 rows=10 loops=1)
-> Index Scan using parsed1_tlocal_index on
parsed1 (cost=0.00..34790.39 rows=1000000 width=20) (actual time=0.018..0.
025 rows=10 loops=1)
-> Limit (cost=0.00..0.35 rows=10 width=20) (actual
time=0.010..0.024 rows=10 loops=1)
-> Index Scan using parsed2_tlocal_index on
parsed2 (cost=0.00..34758.39 rows=1000000 width=20) (actual time=0.009..0.
015 rows=10 loops=1)
Total runtime: 0.187 ms
(11 rows)
Basically, the second query is giving the same result as the version using
the view but is able to use the indexes because it the order by and limit
are explicitly placed in the sub-queries. So is there a way to make the
planner perform the same sort of operation and push those same constraints
into the sub-queries on its own?
Thanks,
Dave
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: LIMIT and UNION ALL
@ 2011-05-26 17:00 Tom Lane <tgl@sss.pgh.pa.us>
parent: Dave Johansen <davejohansen@gmail.com>
0 siblings, 0 replies; 5+ messages in thread
From: Tom Lane @ 2011-05-26 17:00 UTC (permalink / raw)
To: Dave Johansen <davejohansen@gmail.com>; +Cc: pgsql-performance
Dave Johansen <davejohansen@gmail.com> writes:
> ... So is there a way to make the
> planner perform the same sort of operation and push those same constraints
> into the sub-queries on its own?
No. As was mentioned upthread, there is a solution for this in 9.1,
although it doesn't work in exactly the way you suggest.
regards, tom lane
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2011-05-26 17:00 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2011-05-18 15:26 LIMIT and UNION ALL Dave Johansen <davejohansen@gmail.com>
2011-05-18 15:54 ` Robert Klemme <shortcutter@googlemail.com>
2011-05-26 16:21 ` Dave Johansen <davejohansen@gmail.com>
2011-05-26 17:00 ` Tom Lane <tgl@sss.pgh.pa.us>
2011-05-18 15:59 ` Pavel Stehule <pavel.stehule@gmail.com>
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