X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 9A979DB873 for ; Mon, 14 Nov 2005 15:37:56 -0400 (AST) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 03386-03 for ; Mon, 14 Nov 2005 19:37:52 +0000 (GMT) Received: from hub.org (hub.org [200.46.204.220]) by svr1.postgresql.org (Postfix) with ESMTP id 5222DDB86F for ; Mon, 14 Nov 2005 15:37:53 -0400 (AST) Received: from localhost (unknown [200.46.204.144]) by hub.org (Postfix) with ESMTP id 686F7C08C01 for ; Mon, 14 Nov 2005 23:42:14 -0400 (AST) Received: from hub.org ([200.46.204.220]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 05239-02 for ; Mon, 14 Nov 2005 19:37:51 +0000 (GMT) Received: from ganymede.hub.org (blk-222-82-85.eastlink.ca [24.222.82.85]) by hub.org (Postfix) with ESMTP id 80D2FC08BEC for ; Mon, 14 Nov 2005 23:42:12 -0400 (AST) Received: by ganymede.hub.org (Postfix, from userid 1000) id 520F94A91B; Mon, 14 Nov 2005 15:37:31 -0400 (AST) Received: from localhost (localhost [127.0.0.1]) by ganymede.hub.org (Postfix) with ESMTP id 4DE7D4A88F for ; Mon, 14 Nov 2005 15:37:31 -0400 (AST) X-Return-Path: X-Received: from ganymede.hub.org ([unix socket]) by ganymede.hub.org (Cyrus v2.2.12) with LMTPA; Mon, 14 Nov 2005 09:55:37 -0400 X-Sieve: CMU Sieve 2.2 X-Received: from localhost (localhost [127.0.0.1]) by ganymede.hub.org (Postfix) with ESMTP id 8175B5C1F9 for ; Mon, 14 Nov 2005 09:55:37 -0400 (AST) X-Received: from mail.postgresql.org [200.46.204.71] by localhost with IMAP (fetchmail-6.2.5.2) for scrappy@localhost (single-drop); Mon, 14 Nov 2005 09:55:37 -0400 (AST) X-Received: from svr1.postgresql.org ([unix socket]) by svr1.postgresql.org (Cyrus v2.2.12) with LMTPA; Mon, 14 Nov 2005 09:53:52 -0400 X-Sieve: CMU Sieve 2.2 X-Received: from localhost (av.hub.org [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id B643CD6D16 for ; Mon, 14 Nov 2005 09:53:52 -0400 (AST) X-Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 63542-09 for ; Mon, 14 Nov 2005 13:53:49 +0000 (GMT) X-Greylist: from auto-whitelisted by SQLgrey- X-Received: from svr2.postgresql.org (svr2.postgresql.org [65.19.161.25]) by svr1.postgresql.org (Postfix) with ESMTP id EBDEFDB74E for ; Mon, 14 Nov 2005 09:25:46 -0400 (AST) X-Received: from mailserver.sandvine.com (sandvine.com [199.243.201.138]) by svr2.postgresql.org (Postfix) with ESMTP id 8B3C1F0C06 for ; Mon, 14 Nov 2005 13:25:50 +0000 (GMT) X-MimeOLE: Produced By Microsoft Exchange V6.0.6603.0 content-class: urn:content-classes:message MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----_=_NextPart_001_01C5E91E.D67CC41E" Subject: sort/limit across union all Date: Mon, 14 Nov 2005 08:25:10 -0500 Message-ID: <2BCEB9A37A4D354AA276774EE13FB8C263B0F4@mailserver.sandvine.com> X-MS-Has-Attach: X-MS-TNEF-Correlator: Thread-Topic: sort/limit across union all Thread-Index: AcXpHuwjr78TzCkNQSSpBxjChIGO1w== From: "Marc Morin" To: X-Virus-Scanned: by amavisd-new at hub.org ReSent-Date: Mon, 14 Nov 2005 15:37:27 -0400 (AST) ReSent-From: "Marc G. Fournier" ReSent-To: pgsql-performance@postgresql.org ReSent-Subject: sort/limit across union all ReSent-Message-ID: <20051114153727.M1019@ganymede.hub.org> X-Virus-Scanned: by amavisd-new at hub.org X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=1.851 required=5 tests=[AWL=1.851, HTML_MESSAGE=0.001, UPPERCASE_25_50=0] X-Spam-Score: 1.851 X-Spam-Level: * X-Archive-Number: 200511/381 X-Sequence-Number: 15638 This is a multi-part message in MIME format. ------_=_NextPart_001_01C5E91E.D67CC41E Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: quoted-printable We have a large DB with partitioned tables in postgres. We have had trouble with a ORDER/LIMIT type query. The order and limit are not pushed down to the sub-tables.... =20 CREATE TABLE base ( foo int=20 ); =20 CREATE TABLE bar_0 extra int ) INHERITS (base); ALTER TABLE bar ADD PRIMARY KEY (foo); =20 -- repeated for bar_0... bar_40 =20 SELECT foo FROM base ORDER BY foo LIMIT 10; =20 is real slow. What is required to make the query planner generate the following instead... (code change i know, but how hard would it be?) =20 SELECT foo FROM ( SELECT * FROM bar_0 ORDER BY foo LIMIT 10 UNION ALL SELECT * FROM bar_1 ORDER BY foo LIMIT 10 .... ) AS base ORDER BY foo LIMIT 10; =20 =20 ------_=_NextPart_001_01C5E91E.D67CC41E Content-Type: text/html; charset="us-ascii" Content-Transfer-Encoding: quoted-printable
We = have a large DB=20 with partitioned tables in postgres.   We have had trouble = with a=20 ORDER/LIMIT type query.  The order and limit are not pushed down to = the=20 sub-tables....
 
CREATE = TABLE base=20 (
    foo int
);
 
CREATE = TABLE=20 bar_0
    extra int
) = INHERITS=20 (base);
ALTER = TABLE bar ADD=20 PRIMARY KEY (foo);
 
-- = repeated for=20 bar_0... bar_40
 
SELECT = foo FROM base=20 ORDER BY foo LIMIT 10;
 
is = real slow. What=20 is required to make the query planner generate the following instead... = (code=20 change i know, but how hard would it be?)
 
SELECT
    foo
FROM
(
   =20 SELECT
       =20 *
    FROM = bar_0
    ORDER BY foo = LIMIT=20 10
UNION ALL
    = SELECT
       =20 *
    FROM = bar_1
    ORDER BY foo = LIMIT=20 10
....
) AS base
ORDER BY foo
LIMIT 10;
 
 
------_=_NextPart_001_01C5E91E.D67CC41E--