Can I hide a part of the text by a "More..." link?
How to avoid inlining subquery result columns
Hello Guest		   
  • Login
• Register…
• Start blog		   • Who, Where, When
• What can I do?
• What to Read?		   • Polls
• Avatars
• Interests		   • Cities and Countries
• Random blog
• Users search		   • Search
• Games
• Tests
• QAIX		   • Сообщества
• Talxy Chat
• Horoscope
• Online		  
Зарегистрируйся!
QAIX > PostgreSQL database development > How to avoid inlining subquery result columns 11 September 2003 20:05:37
  Recent blog posts: 
Free iPod nano raffle for…
Aimersoft 2008 Christmas promotion: "Free iPod nano raffle for Chrismas" and "Buy 1 Get…

How to convert Rhapsody to…
Music purchased from Rhapsody is under DRM protection, which stops customers from playing the downloaded&helli­p;

PS3 firmware update adds full…
Eric Lempel, Sony's Director of PlayStation Network Operations, has posted on the official PlayStation 3…

Nintendo makes $6 profit on…
According to Macquarie Securities analyst David Gibson, Nintendo makes $6 USD operating profit on each…

Maintenance in winter
Skin for winter very easy to do, if does not supplement the moisture in time, like persimmons, flat and…

'Jasper' Xbox 360s finally…
'Jasper' Xbox 360s finally available According to a flooding of posts over at xbox-scene, it appears…

How to transfer Napster music…
Music purchased from Napster is under DRM protection, which stops customers from playing the downloaded&helli­p;

The song my brother taught me
Don't let your heart be hardened Don't let your love grow cold May it always stay so childlike May it…

My brother taught me this…
God grant me the serenity to accept the things I cannot change; courage to change the things I can;…

baz-baz-baz-baz pam-parabaram…
baz-baz-baz-baz pam-parabaram baz-baz-baz-baz pam-parabaram baz-baz-baz-baz pam-parabaram-baz ba-ra-ba…

  They have birthday today: 
xiongchong
henna diah
  Forums:   
    topic feeds

• QAIX  all topics, blogs and comments

• PHP web-programming

• Perl web-programming

• ASP web-programming

• Visual Basic Programming

• .Net Development

• C/C++ Programming

• Java Programming

• Javascript web-programming

• Apache HTTP Server

• Web-design

• Web-hosting, dedicated, colocation

• HTML web-programming

• XML development

• General Web-programming

• Content Management Systems

• ColdFusion

• Search Engine Optimization

• Hardware

• Software, Computer Help

• MySQL database development

• PostgreSQL database development

• MS Access database development

• Oracle database development

• MS SQL database development

• Informix database development

• Buy / Sell

• Open discussion

• Anonymous
  Discuss: 
plpgsql - Insert from a record…
Dear Postgresql experts, I'm writing a simple plpgsql function that selects a row from a table…

calling a pl/pgsql function…
Hello, i am tryin to pass an array like argument to a pl/pgsql function but i can not find the correct…

PgDatabase, on heap…
I am having a problem constructing a PgDatabase object. If I create the object on the stack (as a var…

Backup and restore through JDBC
Hello List I am trying to develop, a API to carry through backup and restore through JDBC. I think that…
  Recent forum topics: 
why infix:<div>:(Int, Int…
HaloO, I realized from the typed literal thread that S03 now explicitly states that div on two Ints…

[rt.cpan.org #41329] VMS…
<URL: http://rt.cpan.org/­Ticket/Display.html?­id=41329 > Thanks, applied

how to write literals of some…
Hello, I'm wondering how to write anonymous value literals of some Perl 6 basic types, and I didn't…
  Recent forum comments:
RE: Defining classpath for…
Your WSExample.java file has to be placed under the WAS.HOME/bin folder. Since your java2Wsdl is…

RE: access denied
IUSR account doesn't have access to cmd.exe by default (assuming you are using anonymous…

RE: access denied
Hey guys, I'm totally out of my league here, I don't even know how I got stuck with this problem, it's…

RE: [patch@34995] vms.c - Memory…
On Wed, Dec 3, 2008 at 7:38 PM, John E. Malmberg <wb8tyw@qsl.net> wrote: Thanks, applied as…

RE: how to write literals of some…
HaloO, David Green wrote: And how about 'Num 1.0 === Complex(1,0) === Int 1'? Should all these be…

RE: how to write literals of some…
HaloO, David Green wrote: IIRC, === is defined to compare only values from the same type domain. For…

RE: Support for ensuring invariants…
* David Green <david.green@telus.­net> [2008-12-03 22:00]: Not with a closure trait and…

RE: Support for ensuring invariants…
* Mark J. Reed <markjreed@gmail.co­m> [2008-12-03 20:30]: Yes. Exactly. It’s not a…

RE: Files, Directories, Resources…
* Aristotle Pagaltzis (pagaltzis@gmx.de) [081204 14:38]: So, they start when - you have users pick…

RE: Support for ensuring invariants…
* mark.a.biggar@comca­st.net <mark.a.biggar@comc­ast.net> [2008-12-03 21:45]: I…
  Moderators:
 Administrator
 Protorioris

How to avoid inlining subquery result columns

Greg Stark 11 September 2003 20:05:37
 
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.62 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.21 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.21 rows=1 width=0) (actual time=0.06..0.07 rows=1 loops=1)
Index Cond: (foo_id = $0)
-> Aggregate (cost=3.21..3.21 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.21 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

Add comment
Tom Lane 11 September 2003 20:05:37 [ permanent link ]
 Greg Stark <gsstark@mit.edu> 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

Add comment
 

Add new comment

As:
Login:  Password:  
 
 
  
 
Пожалуйста, относитесь к собеседникам уважительно, не используйте нецензурные слова, не злоупотребляйте заглавными буквами, не публикуйте рекламу и объявления о купле/продаже, а также материалы нарушающие сетевой этикет или УК РФ.

QAIX > PostgreSQL database development > How to avoid inlining subquery result columns 11 September 2003 20:05:37
see also:
Re: [building] ant properties handling
Another Compiler Type For JSP
[NB 3.1] 18 days before the feature…
пройди тесты:
see also:
be continue
how or why
  Copyright © 2001—2008 QAIX
Idea: Miсhael Monashev
Помощь и задать вопросы можно в сообществе support.qaix.com.
Сообщения об ошибках оставляем в сообществе bugs.qaix.com.
Предложения и комментарии пишем в сообществе suggest.qaix.com.
Информация для родителей.
Write us at:
If you would like to report an abuse of our service, such as a spam message, please .