Re: [SQL] is it possible to use arrays in plpgsql function??
hi, I had the same question a couple of days ago and I received some good helps. look at the archives... regards Omid >From: Bhuvan A <[EMAIL PROTECTED]> >To: [EMAIL PROTECTED] >Subject: [SQL] is it possible to use arrays in plpgsql function?? >Date: Tue, 21 Aug 2001 11:24:08 +0530 (IST) > > >hi all, > >Is it possible to use arrays of any datatypes in plpgsql function? > >If yes, how?? > >thankx. > > >== > Every absurdity has a champion who will defend it. > > >== >Regards, >Bhuvaneswar. > > >---(end of broadcast)--- >TIP 6: Have you searched our list archives? > >http://www.postgresql.org/search.mpl _ Get your FREE download of MSN Explorer at http://explorer.msn.com/intl.asp ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] Getting 'n-1'th record.
hi all, consider below.. An sql query results with 'n' records. OK. I need ONLY the 'n-1'th record. HOW CAN I GET THIS? Thankx in advance! == Q: What's the difference between the 1950's and the 1980's? A: In the 80's, a man walks into a drugstore and states loudly, "I'd like some condoms," and then, leaning over the counter, whispers, "and some cigarettes." == Regards, Bhuvaneswar. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Getting 'n-1'th record.
Hi, It is a not a clean job but how about having a view like this : create view foo_view as select * from yourtable order by oid desc limit 2 ; and then making your select like this: select * from foo_view order by oid limit 1; hope that helps Omid >From: Bhuvan A <[EMAIL PROTECTED]> >To: [EMAIL PROTECTED] >Subject: [SQL] Getting 'n-1'th record. >Date: Tue, 21 Aug 2001 13:11:33 +0530 (IST) > > >hi all, > >consider below.. > >An sql query results with 'n' records. OK. >I need ONLY the 'n-1'th record. > > >HOW CAN I GET THIS? > >Thankx in advance! > > >== > Q: What's the difference between the 1950's and the 1980's? > A: In the 80's, a man walks into a drugstore and states loudly, "I'd > like some condoms," and then, leaning over the counter, whispers, >"and some cigarettes." > > >== > >Regards, >Bhuvaneswar. > > >---(end of broadcast)--- >TIP 4: Don't 'kill -9' the postmaster _ Get your FREE download of MSN Explorer at http://explorer.msn.com/intl.asp ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Primary vs Unique Index
Thus spake Gonzo Rock
> Why would one need a Primary Key... which can only be declared at table creation if
>one can create a Unique Index post table creation?
>
> ie: I deleted my primary key... is that a big deal? What's the purpose of the
>Primary Key if it's function can be duplicated with another Unique Index?
It has to do somewhat with database theory. There is a basic distinction
between a unique index and a primary key. Ideally, the primary key should
never change but a unique key can as long as the new value is also unique.
PostgreSQL doesn't enforce this (I think it should) but a good database
design will include primary keys that can remain constant.
One thing that PostgreSQL allows is for a program to query a database
and determine which key in a table is the primary key. You can use
this in user programs to make decisions like that suggested above. I
use it in PyGreSQL (Classic) to find records based on a dictionary of
values. For example, let's say I have just loaded an address record
into a dictionary and I would like that dictionary to include the full
province name. I have a province table that allows that lookup.
db.get('province', dict)
The get method looks at province and determines that the primary key
is 'prov' so it looks up that key in the dictionary and uses it to
load in (and merge) the province record. If you are familiar with
a 4GL such as Progress you may recognize this soul brother of the
above statement.
FIND province OF address.
--
D'Arcy J.M. Cain| Democracy is three wolves
http://www.druid.net/darcy/| and a sheep voting on
+1 416 425 1212 (DoD#0082)(eNTP) | what's for dinner.
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/users-lounge/docs/faq.html
[SQL] RE: Sequential select queries...??
If you want to know for each individual one wether both equal or not you could do: SELECT *,NOT ((name=description) IS NULL) AND (name=description) AS both FROM t WHERE name='bleh' OR description='bleh'; If you wanted totals of the same you could do: SELECT count(*) AS tot,NOT ((name=description) IS NULL) AND (name=description) AS both FROM t WHERE name='bleh' OR description='bleh' GROUP BY NOT ((name=description) IS NULL AND (name=description); I think this should be more efficient than UNIONs, but am not an expert on the query planner or executor. - Stuart > -Original Message- > From: Mark Mikulec [SMTP:[EMAIL PROTECTED]] > Sent: Wednesday, August 15, 2001 1:41 AM > To: [EMAIL PROTECTED] > Subject: Sequential select queries...?? > > Hello, > > At first I thought what I was trying to do was simple and could be done > easily - but alas, I've spent way too much time and could not figure out > how to get the results in question. > > Let's say I have a table T comprised of id of type integer, name and > description both of type text. > > What i'd like to do is the following: > > Select id from T where name = 'bleh'; > > and > > Select id from T where description = 'bleh'; > > and result both results in the same result set. That is, duplicate id's > if they appear. So then I could do a GROUP BY and a COUNT to see how > many appeared in only one, and how many appeared in both. > > Could someone help me? I've tried countless different sql queries, can't > seem to get one to work. If I can just get those duplicate id's in the > query.. then I'd be laughing and then I can complete my task. > > Thanks in advance, > > Mark > ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: [SQL] database location question
Carolyn Lu Wong <[EMAIL PROTECTED]> writes: > I'm using V6.5.3. You really oughta update ;-) > Is there a way to move the database to another directory location > instead in /var/lib/pgsql? Sure, just shut down the postmaster, physically move the entire data tree (use 'cp -p -r' or 'tar' or some such), put a symlink to the new place at /var/lib/pgsql/data, and away you go. Or forget the symlink and instead tell the postmaster where the data tree is with a -D switch. regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Getting 'n-1'th record.
Bhuvan A <[EMAIL PROTECTED]> writes: > I need ONLY the 'n-1'th record. See the 'LIMIT' and 'OFFSET' clauses in SELECT. Note the caveat that you'd better ORDER the rows to be sure you know which is the n-1'th. Having done an ORDER BY, you could simplify your life by reversing the ordering and choosing the second row (OFFSET 1 LIMIT 1). regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] Re: split/explode functions
Not amongst the builtin functions. You will need to create your own using a procedural language. Easiest is plperl, since Perl already has a very functional "split" function. - Original Message - From: "omid omoomi" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Monday, August 20, 2001 7:31 PM Subject: split/explode functions > hi , > Is there any split or explode function which split a whole string to > seperate parts using any special delimiter ? > TIA > Omid > > _ > Get your FREE download of MSN Explorer at http://explorer.msn.com/intl.asp > > > ---(end of broadcast)--- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED]) > > ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] Getting 'n-1'th record.
Bhuvan, > An sql query results with 'n' records. OK. > I need ONLY the 'n-1'th record. You're making this much harder than it needs to be. If you want the "nth" record, then you have to be supplying the database with an ORDER BY. For the next-to-last record, simply reverse the ORDER BY and take the second record. e.g.: If you want the next-to-last (n-1) record from: SELECT * FROM syslog ORDER BY entrytime; Then ask for: SELECT * FROM syslog ORDER BY entrytime DESC LIMIT 1 OFFSET 1; Easy, no? -Josh Berkus __AGLIO DATABASE SOLUTIONS___ Josh Berkus Complete information technology [EMAIL PROTECTED] and data management solutions (415) 565-7293 for law firms, small businessesfax 621-2533 and non-profit organizations. San Francisco ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Primary vs Unique Index
Darcy, > It has to do somewhat with database theory. There is a basic > distinction > between a unique index and a primary key. Ideally, the primary key > should > never change but a unique key can as long as the new value is also > unique. > PostgreSQL doesn't enforce this (I think it should) but a good > database > design will include primary keys that can remain constant. To add my .02 here ... I'd prefer it if PostgreSQL required a Primary Key at table creation, and forced drop-and-recreate if you attempt to modify the primary key. Frankly, I regard the failure to enforce Primary Key requirements to be a PostgreSQL bug, even if Tom & Peter don't. A strong Primary Key feature is absolutely required if we want to later build more advanced ANSI SQL 92 features into PostgreSQL. For example, if we wanted to improve Postgres so that simple views would be updatable without writing a lot of custom triggers, then all tables must have a Primary Key as a prerequisite. Or, some more common problems: If you created a Key-less table, with a seperate unique index, you'd run into two problems: 1. Any RDBMS or interface features meant to grab the Primary Key would fail. An example of this is MS ODBC, which requires a Primary Key to make a linked table updatable. 2. If someone later modified your unique index to make it not-unique, you might find yourself with a table with no unique indexes. In this circumstance, the database engine has a hard time identifying individual rows, and will tend to UPDATE/DELETE entire sets of rows based on common characteristics. Fabian Pascal, in "Practical Issues in Database Management" talks further about the importance of primary keys. __AGLIO DATABASE SOLUTIONS___ Josh Berkus Complete information technology [EMAIL PROTECTED] and data management solutions (415) 565-7293 for law firms, small businessesfax 621-2533 and non-profit organizations. San Francisco ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] exists
Thank you, I was missing the parens. If I do an explain I see: -> Index Scan using m_u_and_p_key on m (cost=0.00..3035.22 rows=1363 width=12) even if I put a limit 1 on the select. Why is that? Stephan Szabo wrote: > On Mon, 20 Aug 2001, Joseph Shraibman wrote: > > >>I want to select a boolean if there exists a row in another table that matches this >one. >>So I did select ..., (select count(*) from table2 where ...) > 0 ... >>but that count(*) was taking forever. I know there is a better way to do it, but >whenever >>I try to use EXISTS I get a syntax error. What is the proper way? >> > > Hmm, on current sources I can do: > select ..., exists (select * from table2 where ...) from table1; > > I don't know if that's new though... > -- Joseph Shraibman [EMAIL PROTECTED] Increase signal to noise ratio. http://www.targabot.com ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[SQL] Re: Getting 'n-1'th record.
SELECT * FROM table ORDER BY field DESC LIMIT 1 OFFSET 1; This way you don't need to know the value of "n" in advance. The descending ORDER BY is to indicate a reversal of your intended ordering, so as to make the n-1'th record the second record. Note that getting the n-1'th record from an unordered list will not give meaningful results, because you cannot predict what will be returned. - Original Message - From: "Bhuvan A" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Tuesday, August 21, 2001 2:41 AM Subject: Getting 'n-1'th record. > > hi all, > > consider below.. > > An sql query results with 'n' records. OK. > I need ONLY the 'n-1'th record. > > > HOW CAN I GET THIS? > > Thankx in advance! > > == > Q: What's the difference between the 1950's and the 1980's? > A: In the 80's, a man walks into a drugstore and states loudly, "I'd > like some condoms," and then, leaning over the counter, whispers, >"and some cigarettes." > > == > > Regards, > Bhuvaneswar. > > > ---(end of broadcast)--- > TIP 4: Don't 'kill -9' the postmaster > > ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] exists
On Tue, 21 Aug 2001, Joseph Shraibman wrote: > Thank you, I was missing the parens. > > If I do an explain I see: > > -> Index Scan using m_u_and_p_key on m (cost=0.00..3035.22 rows=1363 width=12) > > > even if I put a limit 1 on the select. Why is that? Is that the inner query (on the exists) or the entire explain? I guess it'd be useful to see the whole query and explain and maybe schema. > Stephan Szabo wrote: > > On Mon, 20 Aug 2001, Joseph Shraibman wrote: > > > > > >>I want to select a boolean if there exists a row in another table that matches >this one. > >>So I did select ..., (select count(*) from table2 where ...) > 0 ... > >>but that count(*) was taking forever. I know there is a better way to do it, but >whenever > >>I try to use EXISTS I get a syntax error. What is the proper way? > >> > > > > Hmm, on current sources I can do: > > select ..., exists (select * from table2 where ...) from table1; > > > > I don't know if that's new though... ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] exists
Stephan Szabo wrote: >>Limit (cost=48.39..48.39 rows=1 width=70) >> -> Sort (cost=48.39..48.39 rows=2 width=70) >> -> Hash Join (cost=18.46..48.38 rows=2 width=70) >> -> Index Scan using u_p_key on u (cost=0.00..27.66 rows=48 width=28) >> -> Hash (cost=18.39..18.39 rows=28 width=42) >> -> Seq Scan on d (cost=0.00..18.39 rows=28 width=42) >> SubPlan >> -> Nested Loop (cost=0.00..4.04 rows=1 width=20) >> -> Index Scan using a_pkey on a (cost=0.00..2.01 rows=1 >width=4) >> -> Index Scan using p_pkey on pu (cost=0.00..2.02 rows=1 >width=16) >> -> Index Scan using m_u_and_p_key on m (cost=0.00..3035.22 >rows=1363 >>width=44) >> > > At least, what was the query that generated this and is it running > slowly or otherwise giving problems? The total explain doesn't seem > unreasonable to my relatively untrained eyes in the absense of knowing the > query :) > Well the total cost should be at least as big as the sub-costs, no? Doesn't that seem strange? -- Joseph Shraibman [EMAIL PROTECTED] Increase signal to noise ratio. http://www.targabot.com ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] exists
Stephan Szabo wrote: > On Tue, 21 Aug 2001, Joseph Shraibman wrote: > > >>Thank you, I was missing the parens. >> >>If I do an explain I see: >> >>-> Index Scan using m_u_and_p_key on m (cost=0.00..3035.22 rows=1363 width=12) >> >> >>even if I put a limit 1 on the select. Why is that? >> > > Is that the inner query (on the exists) or the entire explain? Just the inner query > > I guess it'd be useful to see the whole query and explain and maybe > schema. That's big and complicated. Can you reproduce this somewhere else? Here is the whole explain: Limit (cost=48.39..48.39 rows=1 width=70) -> Sort (cost=48.39..48.39 rows=2 width=70) -> Hash Join (cost=18.46..48.38 rows=2 width=70) -> Index Scan using u_p_key on u (cost=0.00..27.66 rows=48 width=28) -> Hash (cost=18.39..18.39 rows=28 width=42) -> Seq Scan on d (cost=0.00..18.39 rows=28 width=42) SubPlan -> Nested Loop (cost=0.00..4.04 rows=1 width=20) -> Index Scan using a_pkey on a (cost=0.00..2.01 rows=1 width=4) -> Index Scan using p_pkey on pu (cost=0.00..2.02 rows=1 width=16) -> Index Scan using m_u_and_p_key on m (cost=0.00..3035.22 rows=1363 width=44) I tried to make a simple test case: create table ta (a int); create table tb (c int, b int); insert into ta values (2); insert into ta values (4); insert into ta values (6); insert into ta values (8); insert into ta values (10); insert into tb values (1,1); insert into tb values (2,2); insert into tb values (3,3); insert into tb values (4,4); vacuum analyze; select c, b , exists(select a from ta where a = c) from tb; explain select c, b , exists(select a from ta where a = c) from tb; drop table ta; drop table tb; ... but the data is so small it uses a seq scan: Seq Scan on tb (cost=0.00..1.04 rows=4 width=8) SubPlan -> Seq Scan on ta (cost=0.00..1.06 rows=1 width=4) > > >>Stephan Szabo wrote: >> >>>On Mon, 20 Aug 2001, Joseph Shraibman wrote: >>> >>> >>> I want to select a boolean if there exists a row in another table that matches >this one. So I did select ..., (select count(*) from table2 where ...) > 0 ... but that count(*) was taking forever. I know there is a better way to do it, but >whenever I try to use EXISTS I get a syntax error. What is the proper way? >>>Hmm, on current sources I can do: >>>select ..., exists (select * from table2 where ...) from table1; >>> >>>I don't know if that's new though... >>> -- Joseph Shraibman [EMAIL PROTECTED] Increase signal to noise ratio. http://www.targabot.com ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: [SQL] exists
> Stephan Szabo wrote: > > On Tue, 21 Aug 2001, Joseph Shraibman wrote: > > > > > >>Thank you, I was missing the parens. > >> > >>If I do an explain I see: > >> > >>-> Index Scan using m_u_and_p_key on m (cost=0.00..3035.22 rows=1363 width=12) > >> > >> > >>even if I put a limit 1 on the select. Why is that? > >> > > > > Is that the inner query (on the exists) or the entire explain? > > Just the inner query > > > > > I guess it'd be useful to see the whole query and explain and maybe > > schema. > > That's big and complicated. Can you reproduce this somewhere else? I've only tried small data sets so nothing terribly meaningful. > > Here is the whole explain: > > Limit (cost=48.39..48.39 rows=1 width=70) >-> Sort (cost=48.39..48.39 rows=2 width=70) > -> Hash Join (cost=18.46..48.38 rows=2 width=70) >-> Index Scan using u_p_key on u (cost=0.00..27.66 rows=48 width=28) >-> Hash (cost=18.39..18.39 rows=28 width=42) > -> Seq Scan on d (cost=0.00..18.39 rows=28 width=42) >SubPlan > -> Nested Loop (cost=0.00..4.04 rows=1 width=20) >-> Index Scan using a_pkey on a (cost=0.00..2.01 rows=1 >width=4) >-> Index Scan using p_pkey on pu (cost=0.00..2.02 rows=1 >width=16) > -> Index Scan using m_u_and_p_key on m (cost=0.00..3035.22 >rows=1363 > width=44) At least, what was the query that generated this and is it running slowly or otherwise giving problems? The total explain doesn't seem unreasonable to my relatively untrained eyes in the absense of knowing the query :) ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: [SQL] exists
Joseph Shraibman <[EMAIL PROTECTED]> writes: > Well the total cost should be at least as big as the sub-costs, no? Not if the sub-plan in question is for an EXISTS. The sub-plan cost is stated in terms of cost to retrieve all rows --- but the outer level EXISTS isn't going to retrieve all rows, it's going to stop as soon as it gets even one. So the cost estimate that propagates up is 3035.22/1363. BTW, this sort of consideration is why 7.0 and later state plan costs in terms of startup and total cost: if a plan has a nontrivial startup cost, just dividing total cost by number of tuples isn't a good way to estimate the costs of partial retrieval. Really the cost estimate is figured as startup_cost + (total_cost-startup_cost) * tuples_retrieved/total_tuples. This is important for EXISTS, LIMIT, and maybe a couple other things. Without this, we'd not be bright enough to choose fast-startup plans over least-total-cost plans in cases where fast-startup is what you want. regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[SQL] Should I worry?
Folks: Do I need to worry about this: pq_flush: send() failed: Broken pipe ... which appears in the log intermittently? __AGLIO DATABASE SOLUTIONS___ Josh Berkus Complete information technology [EMAIL PROTECTED] and data management solutions (415) 565-7293 for law firms, small businessesfax 621-2533 and non-profit organizations. San Francisco ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] exists
Then why does the explain say rows=1363 ? I don't mean to nitpick here, but maybe this is the symptom of a larger problem. Tom Lane wrote: > Joseph Shraibman <[EMAIL PROTECTED]> writes: > >>Well the total cost should be at least as big as the sub-costs, no? >> > > Not if the sub-plan in question is for an EXISTS. The sub-plan cost > is stated in terms of cost to retrieve all rows --- but the outer level > EXISTS isn't going to retrieve all rows, it's going to stop as soon as > it gets even one. So the cost estimate that propagates up is > 3035.22/1363. > > BTW, this sort of consideration is why 7.0 and later state plan costs > in terms of startup and total cost: if a plan has a nontrivial startup > cost, just dividing total cost by number of tuples isn't a good way to > estimate the costs of partial retrieval. Really the cost estimate is > figured as > startup_cost + (total_cost-startup_cost) * tuples_retrieved/total_tuples. > This is important for EXISTS, LIMIT, and maybe a couple other things. > Without this, we'd not be bright enough to choose fast-startup plans > over least-total-cost plans in cases where fast-startup is what you want. > > regards, tom lane > -- Joseph Shraibman [EMAIL PROTECTED] Increase signal to noise ratio. http://www.targabot.com ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Should I worry?
"Josh Berkus" <[EMAIL PROTECTED]> writes: > Do I need to worry about this: > pq_flush: send() failed: Broken pipe > ... which appears in the log intermittently? Looks like the trace of a client disconnecting ungracefully (mid-query). If you're not aware of any client-side failures in your setup then you should perhaps look more closely, but otherwise this is not something to worry about. The backend will roll back its transaction and close up shop just fine. I think we have a TODO item to suppress multiple occurrences of this log message, but otherwise that stuff works fine AFAIK. regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: [SQL] exists
Joseph Shraibman <[EMAIL PROTECTED]> writes: > Then why does the explain say rows=1363 ? That's the estimate of how many rows the inner SELECT would return, if left free to return them all. You should get the same row count estimate (though quite possibly a different plan) if you just do an EXPLAIN of the sub-select that you have inside EXISTS. Of course you can't easily do that if the sub-select has outer references, but consider this simplified example from the regression database: regression=# explain select * from tenk1; NOTICE: QUERY PLAN: Seq Scan on tenk1 (cost=0.00..333.00 rows=1 width=148) EXPLAIN regression=# explain select * from tenk1 where unique1 > 9000; NOTICE: QUERY PLAN: Seq Scan on tenk1 (cost=0.00..358.00 rows=1045 width=148) EXPLAIN regression=# explain select unique2, regression-# exists(select * from tenk1 where unique1 > 9000), regression-# unique1 from tenk1; NOTICE: QUERY PLAN: Seq Scan on tenk1 (cost=0.00..333.00 rows=1 width=8) InitPlan -> Seq Scan on tenk1 (cost=0.00..358.00 rows=1045 width=148) EXPLAIN regression=# explain select unique2, regression-# exists(select * from tenk1 where unique1 > out1.unique2), regression-# unique1 from tenk1 out1; NOTICE: QUERY PLAN: Seq Scan on tenk1 out1 (cost=0.00..333.00 rows=1 width=8) SubPlan -> Seq Scan on tenk1 (cost=0.00..358.00 rows= width=148) EXPLAIN In the last case the rows count has changed from a statistics-driven estimate to a default estimate because the planner doesn't have any idea what out1.unique2 will be on any given execution of the subplan. > I don't mean to nitpick here, but maybe this is the symptom of a > larger problem. The only "larger problem" I see here is that the planner doesn't bother to estimate the costs of expensive expressions in the SELECT target list; you can see by comparing the above examples that the total cost at the top level doesn't include the obviously-nontrivial cost of evaluating the EXIST expressions. This however is not a bug, but a deliberate simplification to save planning time. The planner cannot alter the number of times the SELECT output expressions are evaluated (at least not if it's delivering the right answer) so there's no point in worrying whether they are expensive or cheap. But it would include the costs of an EXISTS appearing in WHERE. regards, tom lane ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] exists
Why does explain show more than one row, even if there is a LIMIT = 1? -- Joseph Shraibman [EMAIL PROTECTED] Increase signal to noise ratio. http://www.targabot.com ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] exists
Joseph Shraibman <[EMAIL PROTECTED]> writes: > Why does explain show more than one row, even if there is a LIMIT = 1? What version are you running? I get results like regression=# explain select * from tenk1 limit 1; NOTICE: QUERY PLAN: Limit (cost=0.00..0.03 rows=1 width=148) -> Seq Scan on tenk1 (cost=0.00..333.00 rows=1 width=148) EXPLAIN which seems at least moderately self-explanatory. regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: [SQL] exists
I'm running 7.1.3. What does 'rows=1' mean? The number of rows returned or the number postgres has to look through? Tom Lane wrote: > Joseph Shraibman <[EMAIL PROTECTED]> writes: > >>Why does explain show more than one row, even if there is a LIMIT = 1? >> > > What version are you running? I get results like > > regression=# explain select * from tenk1 limit 1; > NOTICE: QUERY PLAN: > > Limit (cost=0.00..0.03 rows=1 width=148) > -> Seq Scan on tenk1 (cost=0.00..333.00 rows=1 width=148) > > EXPLAIN > > which seems at least moderately self-explanatory. > > regards, tom lane > -- Joseph Shraibman [EMAIL PROTECTED] Increase signal to noise ratio. http://www.targabot.com ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] Altering pg_conndefaults
how do i alter values of pg_conndefaults? example user=user1 to user=user2 thanks in advance ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
