Received: from maia.hub.org (maia-5.hub.org [200.46.204.29]) by mail.postgresql.org (Postfix) with ESMTP id E8B8BB5DBBF for ; Thu, 26 May 2011 13:46:18 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.29]) (amavisd-maia, port 10024) with ESMTP id 36507-04 for ; Thu, 26 May 2011 16:45:36 +0000 (UTC) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from mail-yx0-f174.google.com (mail-yx0-f174.google.com [209.85.213.174]) by mail.postgresql.org (Postfix) with ESMTP id 08171B5DBF7 for ; Thu, 26 May 2011 13:45:35 -0300 (ADT) Received: by yxs7 with SMTP id 7so355392yxs.19 for ; Thu, 26 May 2011 09:45:35 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:mime-version:in-reply-to:references:date :message-id:subject:from:to:content-type; bh=7itbutO81sR2m9Y5QySiy018cPBrLJzA8ztL215uDYY=; b=m9PUrqJx5Uazy+u1VIFclQpsALNpsYC3sv/l3AwtIf2LkKGxcBT9/y/OCXPmTmzWwL GWlVDR8SuC3vOC6oPhMjv6sxQ4+YPJLuB4Jvg83vySBCK5mcmXaQajlzOq5pH9457i0w p2mDiq/WcUgcj5NFSz65fEPuWRj6HKsqWtChc= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=mime-version:in-reply-to:references:date:message-id:subject:from:to :content-type; b=QDnw143rVqmrGcU/3QTr16hDrqGbuyWFNL8vYiNvwY/tp3paCcavWoPm6+/PWAPKvc +f38OcXYp3K88kVCC73jUQk4YTiexsLuxb9p13ThH5UaNTkaVpWszMG0igk7LrK/Zsun jbh1MzOZdRVb53czBmfx63n2z8e7fTZ1Ex3Gg= MIME-Version: 1.0 Received: by 10.236.70.72 with SMTP id o48mr1151853yhd.512.1306426905577; Thu, 26 May 2011 09:21:45 -0700 (PDT) Received: by 10.236.208.199 with HTTP; Thu, 26 May 2011 09:21:45 -0700 (PDT) In-Reply-To: References: Date: Thu, 26 May 2011 09:21:45 -0700 Message-ID: Subject: Re: LIMIT and UNION ALL From: Dave Johansen To: pgsql-performance Content-Type: multipart/alternative; boundary=000e0cd75494a1c7e204a43037e1 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.897 tagged_above=-10 required=5 tests=BAYES_00=-1.9, FREEMAIL_FROM=0.001, HTML_MESSAGE=0.001, RFC_ABUSE_POST=0.001 X-Spam-Level: X-Archive-Number: 201105/380 X-Sequence-Number: 43889 --000e0cd75494a1c7e204a43037e1 Content-Type: text/plain; charset=ISO-8859-1 On Wed, May 18, 2011 at 8:54 AM, Robert Klemme wrote: > On Wed, May 18, 2011 at 5:26 PM, Dave Johansen > 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 --000e0cd75494a1c7e204a43037e1 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable
On Wed, May 18, 2011 at 8:54 AM, Robert Klemme <= span dir=3D"ltr"><shortcut= ter@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 tw= o
> 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 v= iew to
> perform like the query with the LIMIT explicitly placed in the sub-que= ries?

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. =A0Since 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. =A0Although 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://blo= g.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.

=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0= =A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 Table "public.mess= age1"
=A0Column |=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 Type=A0=A0=A0=A0=A0=A0=A0=A0=A0= =A0 |=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 Mod= ifiers=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=
--------+--------------------------+----------------------------------= ----------------------
=A0rid=A0=A0=A0 | integer=A0=A0=A0=A0=A0=A0=A0=A0= =A0=A0=A0=A0=A0=A0=A0=A0=A0 | not null default nextval('message1_rid_se= q'::regclass)
=A0data=A0=A0 | integer=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 = |
=A0tlocal | timestamp with time zone |
Indexes:
=A0=A0=A0 &quo= t;message1_pkey" PRIMARY KEY, btree (rid)
Referenced by:
=A0=A0= =A0 TABLE "parsed1" CONSTRAINT "parsed1_msgid_fkey" FOR= EIGN KEY (msgid) REFERENCES message1(rid)

=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0= =A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 Table "public.parsed1"
=A0Colum= n |=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 Type=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 |=A0= =A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 Modifiers= =A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0
-= -------+--------------------------+----------------------------------------= ----------------
=A0rid=A0=A0=A0 | integer=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0= =A0 | not null default nextval('parsed1_rid_seq'::regclass)
=A0m= sgid=A0 | integer=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 |
= =A0data=A0=A0 | integer=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 = |
=A0tlocal | timestamp with time zone |
Indexes:
=A0=A0=A0 "parsed1_pkey" PRIMARY KEY, btree (rid)
= Foreign-key constraints:
=A0=A0=A0 "parsed1_msgid_fkey" FOREIG= N KEY (msgid) REFERENCES message1(rid) ON DELETE CASCADE

For this ex= ample, message2 has the same structure/definition and message1 and parsed2 = has the same structure/definition as parsed1.

=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 View "public.parsed_all"
= =A0Column |=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 Type=A0=A0=A0=A0=A0=A0=A0=A0=A0= =A0 | Modifiers
--------+--------------------------+-----------
=A0r= id=A0=A0=A0 | integer=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 | =
=A0msgid=A0 | integer=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0= =A0 |
=A0data=A0=A0 | integer=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 = |
=A0tlocal | timestamp with time zone |
View definition:
=A0=A0= =A0=A0=A0=A0=A0=A0 SELECT parsed1.rid, parsed1.msgid, parsed1.data, parsed1= .tlocal
=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 FROM parsed1
UNION ALL
=A0= =A0=A0=A0=A0=A0=A0=A0 SELECT parsed2.rid, parsed2.msgid, parsed2.data, pars= ed2.tlocal
=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 FROM parsed2;




Slow versio= n using the view:

EXPLAIN ANALYZE SELECT * FROM parsed_all ORDER BY = tlocal DESC LIMIT 10;
=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0= =A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0= =A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 QUERY= PLAN=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0= =A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0= =A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0
---------------------------------------------------------------------------= -----------------------------------------------------------------
=A0Lim= it=A0 (cost=3D74985.28..74985.31 rows=3D10 width=3D20) (actual time=3D6224.= 229..6224.244 rows=3D10 loops=3D1)
=A0=A0 ->=A0 Sort=A0 (cost=3D74985.28..79985.28 rows=3D2000000 width=3D2= 0) (actual time=3D6224.226..6224.230 rows=3D10 loops=3D1)
=A0=A0=A0=A0= =A0=A0=A0=A0 Sort Key: parsed1.tlocal
=A0=A0=A0=A0=A0=A0=A0=A0 Sort Meth= od:=A0 top-N heapsort=A0 Memory: 17kB
=A0=A0=A0=A0=A0=A0=A0=A0 ->=A0 = Result=A0 (cost=3D0.00..31766.00 rows=3D2000000 width=3D20) (actual time=3D= 0.026..4933.210 rows=3D2000000 loops=3D1)
=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 ->=A0 Append=A0 (cost=3D0.00.= .31766.00 rows=3D2000000 width=3D20) (actual time=3D0.024..2880.868 rows=3D= 2000000 loops=3D1)
=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0= =A0=A0=A0 ->=A0 Seq Scan on parsed1=A0 (cost=3D0.00..15883.00 rows=3D100= 0000 width=3D20) (actual time=3D0.023..551.870 rows=3D1000000 loops=3D1) =A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 ->=A0 Seq S= can on parsed2=A0 (cost=3D0.00..15883.00 rows=3D1000000 width=3D20) (actual= time=3D0.027..549.465 rows=3D1000000 loops=3D1)
=A0Total 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 ORD= ER BY tlocal DESC LIMIT 10) AS a UNION ALL SELECT * FROM (SELECT * FROM par= sed2 ORDER BY tlocal DESC LIMIT 10) AS b) AS c ORDER BY tlocal DESC LIMIT 1= 0;
=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0= =A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0= =A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0= =A0=A0=A0 QUERY PLAN=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0= =A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0= =A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0
=A0=A0=A0=A0=A0=A0=A0=A0=A0= =A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0
-------------------------------------= ---------------------------------------------------------------------------= -----------------------------------
---------------------
=A0Limit=A0 (cost=3D1.33..1.35 rows=3D10 width=3D2= 0) (actual time=3D0.131..0.145 rows=3D10 loops=3D1)
=A0=A0 ->=A0 Sort= =A0 (cost=3D1.33..1.38 rows=3D20 width=3D20) (actual time=3D0.129..0.132 ro= ws=3D10 loops=3D1)
=A0=A0=A0=A0=A0=A0=A0=A0 Sort Key: parsed1.tlocal
=A0=A0=A0=A0=A0=A0=A0=A0 Sort Method:=A0 quicksort=A0 Memory: 17kB
=A0= =A0=A0=A0=A0=A0=A0=A0 ->=A0 Result=A0 (cost=3D0.00..0.90 rows=3D20 width= =3D20) (actual time=3D0.023..0.100 rows=3D20 loops=3D1)
=A0=A0=A0=A0=A0= =A0=A0=A0=A0=A0=A0=A0=A0=A0 ->=A0 Append=A0 (cost=3D0.00..0.90 rows=3D20= width=3D20) (actual time=3D0.020..0.078 rows=3D20 loops=3D1)
=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 ->=A0 Limit= =A0 (cost=3D0.00..0.35 rows=3D10 width=3D20) (actual time=3D0.020..0.035 ro= ws=3D10 loops=3D1)
=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0= =A0=A0=A0=A0=A0=A0=A0=A0=A0 ->=A0 Index Scan using parsed1_tlocal_index = on parsed1=A0 (cost=3D0.00..34790.39 rows=3D1000000 width=3D20) (actual tim= e=3D0.018..0.
025 rows=3D10 loops=3D1)
=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0= =A0=A0=A0=A0=A0 ->=A0 Limit=A0 (cost=3D0.00..0.35 rows=3D10 width=3D20) = (actual time=3D0.010..0.024 rows=3D10 loops=3D1)
=A0=A0=A0=A0=A0=A0=A0= =A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 ->=A0 Index Sc= an using parsed2_tlocal_index on parsed2=A0 (cost=3D0.00..34758.39 rows=3D1= 000000 width=3D20) (actual time=3D0.009..0.
015 rows=3D10 loops=3D1)
=A0Total runtime: 0.187 ms
(11 rows)

=
Basically, the second query is giving the same result as the version us= ing the view but is able to use the indexes because it the order by and lim= it 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
--000e0cd75494a1c7e204a43037e1--