Re: [SQL] outer joins strangeness
On Sun, 23 Sep 2001, Alex Pilosov wrote: > It may be just me, or I am grossly misunderstanding syntax of outer joins, > but I see that plans for my queries are different depending on how I place > join conditions and sometimes even on order of the tables. > > Example: > 1: > explain select * from customers c,orders o left outer join adsl_orders ao > on ao.order_id=o.order_id > where c.cust_id=o.cust_id > and c.cust_id=152 > > > Nested Loop (cost=94.23..577.47 rows=2 width=290) > -> Index Scan using customers_pkey on customers c (cost=0.00..2.02 > rows=1 width=125) > -> Materialize (cost=501.65..501.65 rows=5904 width=165) > -> Hash Join (cost=94.23..501.65 rows=5904 width=165) > -> Seq Scan on orders o (cost=0.00..131.04 rows=5904 > width=58) > -> Hash (cost=86.18..86.18 rows=3218 width=107) > -> Seq Scan on adsl_orders ao (cost=0.00..86.18 > rows=3218 width=107) > > Query 2: > > explain select * from customers c join orders o on c.cust_id=o.cust_id > left outer join adsl_orders ao on ao.order_id=o.order_id > where c.cust_id=152 > > Nested Loop (cost=0.00..9.30 rows=2 width=290) > -> Nested Loop (cost=0.00..5.06 rows=2 width=183) > -> Index Scan using customers_pkey on customers c > (cost=0.00..2.02 rows=1 width=125) > -> Index Scan using orders_idx1 on orders o (cost=0.00..3.03 > rows=1 width=58) > -> Index Scan using adsl_orders_pkey on adsl_orders ao > (cost=0.00..2.02 rows=1 width=107) > > To me, both queries seem exactly identical in meaning, and should generate > the same plans. However, in my experience, if I use outer join anywhere in > the query, I must use "JOIN" syntax to join all other tables as well, > otherwise, my query plans are _extremely_ slow. Postgres treats join syntax as an explicit definition of what order to joins in. So, I'd guess it sees the first as: do the LOJ and then join that to the separate table. And for right outer join (for example), those two queries would not be equivalent if I read the ordering correctly. The former syntax would mean outer first and then the inner, whereas the second would be inner first then the outer, and that could have different results. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] loading array_iterator.so does not work with Postgresql
Frederick, while I have no experience with contrib/array it seems our contrib/intarray would help you. As a benefit you will get indexed access to an array Oleg On Sun, 23 Sep 2001, Frederick Klauschen wrote: > I try to load the array_iterator to find array-entries > without knowing at what position they are. > Compiling worked o.k. and the psql -d > databasename -f /path/array_iterator.sql > worked fine. But then load /path/array_iterator.so > does not work. The library-file is found, but there > seems to be a problem with the "arraygetNitems" > function. > Thanks for any help! > Frederick > > > __ > Do You Yahoo!? > Get email alerts & NEW webcam video instant messaging with Yahoo! Messenger. >http://im.yahoo.com > > ---(end of broadcast)--- > TIP 6: Have you searched our list archives? > > http://archives.postgresql.org > Regards, Oleg _ Oleg Bartunov, sci.researcher, hostmaster of AstroNet, Sternberg Astronomical Institute, Moscow University (Russia) Internet: [EMAIL PROTECTED], http://www.sai.msu.su/~megera/ phone: +007(095)939-16-83, +007(095)939-23-83 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] Bug?: Update on ancestor for a row of a child
Hello (mainly developer) folks! Probably Kevin really found a bug. When I saw his words in $50, I immediately started to look around his problem... You probably don't think that as a student here, in Hungary I live half a month for $50 :- So I simplified his given schema as much as I needed, and found out what exactly the problem is. (Kevin, if you badly need a workaround, I can give you one, but quite an ugly one.) The problem is: when updating a row in an ancestor table, which is really belongs to a child, there's something wrong with the CHECK system. Here's the simple schema, producing the error: drop table child; drop table ancestor; create table ancestor ( node_id int4, a int4 ); create table child ( b int4 NOT NULL DEFAULT 0 CHECK ( b = 0 OR b = 1) ) inherits (ancestor); insert into ancestor values (3,4); insert into child values (5,6,1); update ancestor set a=8 where node_id=5; If one leaves out the CHECK condition, the UPDATE works just fine, and __the final result meets the check condition__. So it seems to me that the system 1. either tries to check the CHECK condition of the child on the ancestor 2. or only uses a wrong time to check against it. Best regards, Baldvin ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] outer joins strangeness
On Sun, 23 Sep 2001, Stephan Szabo wrote: > On Sun, 23 Sep 2001, Alex Pilosov wrote: > > > It may be just me, or I am grossly misunderstanding syntax of outer joins, > > but I see that plans for my queries are different depending on how I place > > join conditions and sometimes even on order of the tables. > > > > Example: > > 1: > > explain select * from customers c,orders o left outer join adsl_orders ao > > on ao.order_id=o.order_id > > where c.cust_id=o.cust_id > > and c.cust_id=152 > > > > explain select * from customers c join orders o on c.cust_id=o.cust_id > > left outer join adsl_orders ao on ao.order_id=o.order_id > > where c.cust_id=152 > Postgres treats join syntax as an explicit definition of what order to > joins in. So, I'd guess it sees the first as: do the LOJ and then join > that to the separate table. Yeah, I figure that's how it sees it, but that's pretty stupid from performance reasons :P) It _should_ realize that left outer join only constricts join order between two tables in outer join, and joins to all other tables should still be treated normally. I'm going to CC this to -hackers, maybe someone will shed a light on the internals of this. > And for right outer join (for example), those two queries would not > be equivalent if I read the ordering correctly. The former syntax > would mean outer first and then the inner, whereas the second would > be inner first then the outer, and that could have different results. True. But this is not right outer join, its a left outer join...:) Postgres should understand that left outer join does not constrict join order... -alex ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] confounding, incorrect constraint error
> > Below is a significantly simplified version of my schema, which > > exhibits > > the above problem. > > Unfortunately, even a simplified version of your schema would take me > some hours to understand. As your rule-setting is quite complex, my > first instinct would be to hunt for circular procedural logic in your > rules. Try to pursue, step by step, everything that happens from the > moment you send the insert command to uservotes. You may find that the > logic cascades back to the beginning. I've done this to myself on > occasion, causing the DB to hang on a seemingly simple request. I'm fairly certain that there's no circular procedural logic. The errors can be turned on/off by turning on/off the uservote_ series of rules, which are attached to the uservote table. These rules call mod_node_vote_count which only touches the node table. There are no rules or triggers associated with the node table, so there is no circular logic there. Additional strangeness is that the itemvote_ series of rules works perfectly despite the fact that the only difference between uservote_ and itemvote_ rules is the table that triggers them, they both call the same procedure on the nodes table. My current thinking is that something is stomping on some memory, because you can vary the effect of the error from being an incorrectly failed CHECK constraint, to crashing the database, by varying the number of columns in the tables in question. I'm unemployed at the moment and this is a pet project, so I can't offer much in the way of financial compensation, but I'll start the bidding at $50 donation in your name to your choice of the EFF, the Red Cross, or the American Cancer Society, in return for a fix. (If none of these charities are acceptable, surely one can be found later that is acceptable to both parties). Again, I greatly appreciate any help, and I apologize that my test case is still fairly sizeable, despite being about 10% the size of the original code. -Kevin Way msg06097/pgp0.pgp Description: PGP signature ---(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
[SQL] outer joins strangeness
It may be just me, or I am grossly misunderstanding syntax of outer joins, but I see that plans for my queries are different depending on how I place join conditions and sometimes even on order of the tables. Basically, if I mix ANSI-syntax outer joins (a left outer join b on a.id=b.id) and "where-syntax" joins (from a,b where a.id=b.id) in the same query, things get strange. Example: 1: explain select * from customers c,orders o left outer join adsl_orders ao on ao.order_id=o.order_id where c.cust_id=o.cust_id and c.cust_id=152 Nested Loop (cost=94.23..577.47 rows=2 width=290) -> Index Scan using customers_pkey on customers c (cost=0.00..2.02 rows=1 width=125) -> Materialize (cost=501.65..501.65 rows=5904 width=165) -> Hash Join (cost=94.23..501.65 rows=5904 width=165) -> Seq Scan on orders o (cost=0.00..131.04 rows=5904 width=58) -> Hash (cost=86.18..86.18 rows=3218 width=107) -> Seq Scan on adsl_orders ao (cost=0.00..86.18 rows=3218 width=107) Query 2: explain select * from customers c join orders o on c.cust_id=o.cust_id left outer join adsl_orders ao on ao.order_id=o.order_id where c.cust_id=152 Nested Loop (cost=0.00..9.30 rows=2 width=290) -> Nested Loop (cost=0.00..5.06 rows=2 width=183) -> Index Scan using customers_pkey on customers c (cost=0.00..2.02 rows=1 width=125) -> Index Scan using orders_idx1 on orders o (cost=0.00..3.03 rows=1 width=58) -> Index Scan using adsl_orders_pkey on adsl_orders ao (cost=0.00..2.02 rows=1 width=107) To me, both queries seem exactly identical in meaning, and should generate the same plans. However, in my experience, if I use outer join anywhere in the query, I must use "JOIN" syntax to join all other tables as well, otherwise, my query plans are _extremely_ slow. any hints? Or I am grossly misunderstanding outer join symantics? -alex ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] Request for book reviews/comments
Folks, Below are several "how-to" book reviews I intend to put up at TechDocs (with Justin's OK). The idea is to provide a list of reference books to reccomend to begineers and other developers, with the caveat that all books must be directly related to PostgreSQL in some way and the reviews will be tailored accordingly. I will start with sections on Postgres, Database Design/Administration and SQL, and I can also picture sections on JDBC books, Programming Open Source Projects, etc. See if you agree with these reviews, and add your own: Postgres Books: PostgreSQL, Introduction and Concepts (by Bruce Momjian, Addison-Wesley, ISBN: 0201703319) The original beginner's manual to PostgreSQL by one of the core developers, Momjian's book contains a spectrum of introductory material on how to get up and running with PostgreSQL while leaving more advanced topics for later books. It includes an introduction to SQL 92 in general as well as Postgres extensions and the basics of Postgres command-line administration. As such, this is probably the perfect book for the user who is brand new to Postgres and inexperienced in SQL. REVIEW WANTED: PostgreSQL by Jeff Perkins REVIEW WANTED: PostgreSQL Programmer's Guide by our own Thomas Lockhart. SQL Books: SQL in a Nutshell (Kline, Kline & Kline, O'Reilly, ISBN: 1565927443) This slim volume is a useful reference for any experienced SQL developer wanting to keep track of the dialectical differences between Postgres, MySQL, MS SQL Server, and Oracle or looking to port a SQL application between platforms. It also has an informative overview of the several SQL standards (92, 99 and SQL 3). As it does not contain any tutorial material and few examples, it is not useful to the beginner. Also the book should not be used as a primary SQL reference dictionary due to weak indexing and several notable omissions. REVIEW WANTED: SQL Queries for Mere Mortals, by Michael Hernandez et al. REVIEW WANTED: SQL for Smarties, by Joe Celko (plus other Joe Celko books) Database Design and Administration Books: Practical Issues in Database Management (Fabian Pascal, Addison-Wesley, ISBN: 0201485559) This book covers in-depth a number of issues that are all too often neglected for the self-educated DBA, including: relational design, normalization, primary keys, and deviations from the SQL standard. All is explained in a completely implementation-agnostic way, with many diagrams and examples. A must for anyone required to create any complex database application from scratch. REVIEW WANTED: Database Design for Mere Mortals, by Michael Hernandez. -Josh __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 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Bug?: Update on ancestor for a row of a child
> The problem is: when updating a row in an ancestor table, > which is really belongs to a child, there's something wrong > with the CHECK system. Well, I believe you found one minor problem. The bigger one is still lurking in the shadows though. To duplicate it, take my previous schema, and add lastlog TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, to the users table, between pass_hash and logged_in. After doing so, you'll find that postgres actually crashes when you try to insert a vote into the uservote table. That's the one that has me looking at the costs involved with migrating to Oracle. -Kevin ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[SQL] problem loading array
__ Do You Yahoo!? Get email alerts & NEW webcam video instant messaging with Yahoo! Messenger. http://im.yahoo.com ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[SQL] loading array_iterator.so does not work with Postgresql 7.0.3/7.1.3
I try to load the array_iterator to find array-entries without knowing at what position they are. Compiling worked o.k. and the psql -d databasename -f /path/array_iterator.sql worked fine. But then load /path/array_iterator.so does not work. The library-file is found, but there seems to be a problem with the "arraygetNitems" function. Thanks for any help! Frederick __ Do You Yahoo!? Get email alerts & NEW webcam video instant messaging with Yahoo! Messenger. http://im.yahoo.com ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[SQL] How to enter lists into database.
I try to find a way to enter a list of items into my database , of which the size should be flexible. I then would like perform queries on that list in a way, that the whole list is retrieved, when the query's items are found to be in that list. Thanks for any help, Frederick __ Do You Yahoo!? Get email alerts & NEW webcam video instant messaging with Yahoo! Messenger. http://im.yahoo.com ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
