subselects vs WITH in views
My assumption was that WITH acted just like subselects, but apparently they
don't? Using WITH doesn't use the expected index.
(the below also at:
https://gist.github.com/joevandyk/839413fac7b3bdd32cb3/raw/cec015d16bed7f4e20ab0101b58ae74a1df1cdc2/gistfile1.txt
create view promotion_details1 as (
select * from (select code from promotions)_
);
create view promotion_details2 as (
with info as (select code from promotions) select * from info
);
explain analyze
select * from promotion_details1 where code = 'slickdeals';
explain analyze
select * from promotion_details2 where code = 'slickdeals';
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on promotions (cost=72.54..6435.31 rows=3014
width=32) (actual time=0.122..0.196 rows=113 loops=1)
Recheck Cond: (code = 'slickdeals'::citext)
-> Bitmap Index Scan on promotions_code_idx (cost=0.00..71.79
rows=3014 width=0) (actual time=0.111..0.111 rows=113 loops=1)
Index Cond: (code = 'slickdeals'::citext)
Total runtime: 0.236 ms
(5 rows)
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------
CTE Scan on info (cost=15539.25..29102.81 rows=3014 width=32)
(actual time=184.303..661.816 rows=113 loops=1)
Filter: (code = 'slickdeals'::citext)
Rows Removed by Filter: 602712
CTE info
-> Seq Scan on promotions (cost=0.00..15539.25 rows=602825
width=32) (actual time=0.018..145.272 rows=602825 loops=1)
Total runtime: 697.495 ms
(6 rows)
Joe Van Dyk wrote:
My assumption was that WITH acted just like subselects, but apparently they don't? Using WITH doesn't
use the expected index.
Currently WITH acts as an "optimization fence", that means
that means that the planner won't move conditions into or
out of the WITH query.
Yours,
Laurenz Albe
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
On Tue, Feb 19, 2013 at 1:02 AM, Albe Laurenz <laurenz.albe@wien.gv.at>wrote:
Joe Van Dyk wrote:
My assumption was that WITH acted just like subselects, but apparently
they don't? Using WITH doesn't
use the expected index.
Currently WITH acts as an "optimization fence", that means
that means that the planner won't move conditions into or
out of the WITH query.
Where's the best place to read up on this?
Thanks,
Joe
On Tue, Feb 19, 2013 at 9:22 AM, Joe Van Dyk <joe@tanga.com> wrote:
On Tue, Feb 19, 2013 at 1:02 AM, Albe Laurenz <laurenz.albe@wien.gv.at>
wrote:Joe Van Dyk wrote:
My assumption was that WITH acted just like subselects, but apparently
they don't? Using WITH doesn't
use the expected index.Currently WITH acts as an "optimization fence", that means
that means that the planner won't move conditions into or
out of the WITH query.Where's the best place to read up on this?
Unfortunately, the mailing list archives. Rightly or wrongly,
postgresql docs are exceptionally light in terms of performance
aspects of various SQL mechanisms.
(non-data modifying) WITH is basically formalization of technique: A
extract to temp table B query that table. Not the optimization fence
characteristic is an implementation detail and not future proofed but
is nevertheless widely replied upon.
merlin
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Hi Merlin,
So should I interpret this as: there is a potential gain from choosing
subqueries over with WITHs ?
On Tue, Feb 19, 2013 at 3:33 PM, Merlin Moncure <mmoncure@gmail.com> wrote:
Show quoted text
On Tue, Feb 19, 2013 at 9:22 AM, Joe Van Dyk <joe@tanga.com> wrote:
On Tue, Feb 19, 2013 at 1:02 AM, Albe Laurenz <laurenz.albe@wien.gv.at>
wrote:Joe Van Dyk wrote:
My assumption was that WITH acted just like subselects, but apparently
they don't? Using WITH doesn't
use the expected index.Currently WITH acts as an "optimization fence", that means
that means that the planner won't move conditions into or
out of the WITH query.Where's the best place to read up on this?
Unfortunately, the mailing list archives. Rightly or wrongly,
postgresql docs are exceptionally light in terms of performance
aspects of various SQL mechanisms.(non-data modifying) WITH is basically formalization of technique: A
extract to temp table B query that table. Not the optimization fence
characteristic is an implementation detail and not future proofed but
is nevertheless widely replied upon.merlin
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
On Thu, Feb 21, 2013 at 4:31 AM, Seref Arikan <serefarikan@gmail.com> wrote:
Hi Merlin,
So should I interpret this as: there is a potential gain from choosing
subqueries over with WITHs ?
Well, potentially, yes. WITH is a mechanic to force iterative order
of evaluation on queries. This can be a good or bad thing naturally.
Subqueries can also do this, especially if you put them in the field
select list -- but WITH is more general. We also have an undocumented
hack that uses OFFSET 0 to force subquery evaluation. These are all
very dangerous tools because they tend to be very sensitive to data
inputs as you are bypassing database statistics effectively. The
other end of the spectrum is to use vanilla JOINs as much as possible
-- this releases the work of planning the query to the database.
Upcoming 9.3 LATERAL will remove one large class of cases where we
have to do this: joining against set returning functions with
non-constant inputs.
merlin
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general