Received: from sss.pgh.pa.us ([192.204.191.242]) by postgresql.org (8.11.3/8.11.1) with ESMTP id f6FLoTa39037 for ; Sun, 15 Jul 2001 17:50:29 -0400 (EDT) (envelope-from tgl@sss.pgh.pa.us) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.11.4/8.11.4) with ESMTP id f6FLoNX11524; Sun, 15 Jul 2001 17:50:23 -0400 (EDT) To: "Josh Berkus" cc: Peter Eisentraut , pgsql-sql@postgresql.org Subject: Re: Hey! ORDER BY in VIEWS? In-reply-to: References: Comments: In-reply-to "Josh Berkus" message dated "Sun, 15 Jul 2001 14:40:30 -0700" Date: Sun, 15 Jul 2001 17:50:23 -0400 Message-ID: <11521.995233823@sss.pgh.pa.us> From: Tom Lane X-Archive-Number: 200107/192 X-Sequence-Number: 3987 "Josh Berkus" writes: >> Hmm, I just realized that there's a bug here: let's say you have >> >> CREATE VIEW latest AS >> SELECT * FROM news ORDER BY story_timestamp DESC LIMIT 1; >> >> ie, this view gives you the latest news story. > Why, exactly, do we need to support ORDER BY ... LIMIT in VIEWS, anyway? > I'm frankly unclear on the utility of this ... I think the above example is pretty compelling, don't you? Easy to read and it generates a very nice indexscan plan. > If we gotta have 'em, though, Tom, you'd have to code in an exception to > the VIEW optimizer that doesn't push down WHERE clauses if the VIEW has > an ORDER BY ... LIMIT statement. Sure you wanna get into this? It's a one-line addition to code that already knows that certain kinds of clauses (like UNION) prevent pushdown. Just an oversight, not a fundamental flaw. regards, tom lane