473,837 Members | 1,498 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

How to avoid inlining subquery result columns


Postgresql 7.4b2 (approximately, compiled out of CVS)

When I have a subquery that has a complex subquery as one of the result
columns, and then that result column is used multiple times in the parent
query, the subquery is inlined for each one. This means multiple redundant
executions of the subquery.

I recall there was a way to defeat this optimization involving introducing an
extra subquery layer somewhere. But I'm failing to be able to reproduce that
now.

What do I have to do to avoid executing the subquery multiple times?
db=> explain
select n,n
from (
select (select count(*) from foo where foo_id = bar.foo_id) as n
from bar
where bar_id = 1
) as x
;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------
Index Scan using bar_pkey on bar (cost=0.00..9.6 2 rows=1 width=4) (actual time=0.41..0.42 rows=1 loops=1)
Index Cond: (bar_id = 1)
SubPlan
-> Aggregate (cost=3.21..3.2 1 rows=1 width=0) (actual time=0.10..0.10 rows=1 loops=1)
-> Index Scan using foo_pkey on foo (cost=0.00..3.2 1 rows=1 width=0) (actual time=0.06..0.07 rows=1 loops=1)
Index Cond: (foo_id = $0)
-> Aggregate (cost=3.21..3.2 1 rows=1 width=0) (actual time=0.14..0.14 rows=1 loops=1)
-> Index Scan using foo_pkey on foo (cost=0.00..3.2 1 rows=1 width=0) (actual time=0.06..0.08 rows=1 loops=1)
Index Cond: (foo_id = $0)
Total runtime: 1.31 msec
(10 rows)

--
greg
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

Nov 11 '05 #1
2 2869
Greg Stark <gs*****@mit.ed u> writes:
I recall there was a way to defeat this optimization involving introducing an
extra subquery layer somewhere.


I think adding "OFFSET 0" to a subquery is the simplest way to prevent
it from being flattened.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

Nov 11 '05 #2
Greg Stark <gs*****@mit.ed u> writes:
I recall there was a way to defeat this optimization involving introducing an
extra subquery layer somewhere.


I think adding "OFFSET 0" to a subquery is the simplest way to prevent
it from being flattened.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

Nov 11 '05 #3

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

7
9237
by: Andrew Mayo | last post by:
Here's a really weird one for any SQL Server gurus out there... We have observed (SQL Server 2000) scenarios where a stored procedure which (a) begins a transaction (b) inserts some rows into a table (c) re-queries another table using a subquery which references the inserted table (correlated or not)
3
13398
by: olanorm | last post by:
I have a query where one or more of the columns returned is a result from a subquery. These columns get their own alias. I want to filter out the rows containing NULL from the subqueries but it just won't work. When running columnAlias IS NOT NULL i just get the error "Invalid column name 'columnAlias'. This is the query: SELECT k.UserId, k.Lastname, k.Firstname, (SELECT kscr.answer FROM Results kscr WHERE kscr.UserID =
0
563
by: Greg Stark | last post by:
Postgresql 7.4b2 (approximately, compiled out of CVS) When I have a subquery that has a complex subquery as one of the result columns, and then that result column is used multiple times in the parent query, the subquery is inlined for each one. This means multiple redundant executions of the subquery. I recall there was a way to defeat this optimization involving introducing an extra subquery layer somewhere. But I'm failing to be able...
2
5383
by: edself | last post by:
Greetings, I am semi-new to Access and have a query question. I presume the solution is easy, but need some help. I have created a database with a Contact table. The contact table contains address fields among other things. Because some contacts share the same address, I included a boolean field, PrimaryContact. If true, a given contact's record contains the address info for that contact. If PrimaryContact is false, then another...
7
2375
by: K. Crothers | last post by:
I administer a mechanical engineering database. I need to build a query which uses the results from a subquery as its input or criterion. I am attempting to find all of the component parts of which a part may be composed. I have a table of parts and their subparts. The problem is that each of those subparts may be composed of smaller component parts. The subpart would then be listed in the Part field linked to each of its subparts in...
5
1473
by: Raphael | last post by:
Hello, I have 2 files A.c : Defining a bunch of usefull functions B.c : Defining a main that uses these ones. I need to improve perfs : I know that, if the caller and the functions called were in the same file, the compiler would automatically inline them if possible.
5
2246
by: steven.fafel | last post by:
I am running 2 versions of a correlated subquery. The two version differ slightly in design but differ tremendously in performance....if anyone can answer this, you would be awesome. The "bad" query attempts to build a result set using a correlated subquery. The part causing the error is that the correlated subquery is part of a derived table (joining 3 tables). Trying to run the query takes a long time and the more records in the...
8
7860
by: kingskippus | last post by:
I don't know if this is possible, but I haven't been able to find any information. I have two tables, for example: Table 1 (two columns, id and foo) id foo --- ----- 1 foo_a 2 foo_b
15
3072
by: Lloyd Dupont | last post by:
I have some code which looks like that: public CornerStyle RectCornerMode { get { return this.GetValue<CornerStyle>(); } set { this.SetValue<CornerStyle>(value); } }
3
1717
by: Bogdan | last post by:
Hi, I've been struggling subqueries/joins and table adapters for some time and can't find a clear answer. I have a stored procedure that returns columns from 2 tables. Two columns from the second table are returned by subquery. All this is done in a stored procedure. I also have a stored procedure that takes most of the params from the select
0
9843
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
10881
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
10575
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
10628
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
1
7807
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
7004
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
5670
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
5850
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
2
4043
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.