Re: [SQL] Order of items in FROM causes error?
Rosser Schwarz <[EMAIL PROTECTED]> writes: > A colleague has the following query, which errors with: relation "dl" > does not exist. (See the second item in the FROM clause.) If that > item is moved to immediately precede the first JOIN item however, the > query works as expected. > select u.usersfirstname || ' ' || u.userslastname as userssupervisorsname > , l.locationsname || ' >> ' || lv.listvaluesname as locationdepartment > , lvcat.listvaluesname as usersemploymentcategory > , lvclass.listvaluesname as usersemploymentclass > , lvacdcat.listcategoriesname as usersacdcategory > from intranet.tbl_users u > , intranet.tbl_departmentslocations dl > , intranet.tbl_listvalues lvcat > , intranet.tbl_listvalues lvclass > , intranet.tbl_listcategories lvacdcat > join intranet.tbl_listvalues lv on dl.listvaluesid = lv.listvaluesid > join intranet.tbl_locations l on dl.locationsid = l.locationsid > where u.usersid = 199 >and dl.listvaluesid = 13 >and lvcat.listvaluesid = 23 >and lvclass.listvaluesid = 27 >and lvacdcat.listcategoriesid = 6 > This strikes me as a bug. No, it isn't, because JOIN binds more tightly than comma. (People coming from MySQL tend to misunderstand this, because MySQL gets it wrong...) regards, tom lane ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] Order of items in FROM causes error?
On Fri, 1 Apr 2005, Rosser Schwarz wrote: > A colleague has the following query, which errors with: relation "dl" > does not exist. (See the second item in the FROM clause.) If that > item is moved to immediately precede the first JOIN item however, the > query works as expected. > > select u.usersfirstname || ' ' || u.userslastname as userssupervisorsname > , l.locationsname || ' >> ' || lv.listvaluesname as locationdepartment > , lvcat.listvaluesname as usersemploymentcategory > , lvclass.listvaluesname as usersemploymentclass > , lvacdcat.listcategoriesname as usersacdcategory > from intranet.tbl_users u > , intranet.tbl_departmentslocations dl > , intranet.tbl_listvalues lvcat > , intranet.tbl_listvalues lvclass > , intranet.tbl_listcategories lvacdcat > join intranet.tbl_listvalues lv on dl.listvaluesid = lv.listvaluesid > join intranet.tbl_locations l on dl.locationsid = l.locationsid > where u.usersid = 199 >and dl.listvaluesid = 13 >and lvcat.listvaluesid = 23 >and lvclass.listvaluesid = 27 >and lvacdcat.listcategoriesid = 6 > > This strikes me as a bug. Is it known behavior? A quick search > doesn't turn up much, but that may be a weakness in my google-fu. SQL seems to say that join binds more tightly than commas, so I don't believe dl is in scope for either of those ON clauses in the explicit join syntax. ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] Order of items in FROM causes error?
A colleague has the following query, which errors with: relation "dl" does not exist. (See the second item in the FROM clause.) If that item is moved to immediately precede the first JOIN item however, the query works as expected. select u.usersfirstname || ' ' || u.userslastname as userssupervisorsname , l.locationsname || ' >> ' || lv.listvaluesname as locationdepartment , lvcat.listvaluesname as usersemploymentcategory , lvclass.listvaluesname as usersemploymentclass , lvacdcat.listcategoriesname as usersacdcategory from intranet.tbl_users u , intranet.tbl_departmentslocations dl , intranet.tbl_listvalues lvcat , intranet.tbl_listvalues lvclass , intranet.tbl_listcategories lvacdcat join intranet.tbl_listvalues lv on dl.listvaluesid = lv.listvaluesid join intranet.tbl_locations l on dl.locationsid = l.locationsid where u.usersid = 199 and dl.listvaluesid = 13 and lvcat.listvaluesid = 23 and lvclass.listvaluesid = 27 and lvacdcat.listcategoriesid = 6 This strikes me as a bug. Is it known behavior? A quick search doesn't turn up much, but that may be a weakness in my google-fu. We're running 7.4.2. (Yes, I know; I'm about to start testing 8.0.2 on our new server.) /rls -- :wq ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] Speed up slow select - was gone blind
Gary Stainburn wrote: Hi folks. I've got my select working now, but I haven't received the speed increase I'd expected. It replaced an earlier select which combined a single explicit join with multiple froms. The first select is the old one, the second one is the new one (with a new join). The new one takes 24 seconds to run while the old one took 29. How can I redo the select to improve the speed, or what else can I do to optimaise the database? You'll want to compare the output of EXPLAIN ANALYSE for each version. Post them here, or on the performance list. Also, make sure your basic PG tuning is ok. http://www.powerpostgresql.com/PerfList -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Speed up slow select - was gone blind
Try with creating INDEX on the used tables...It will make your search query faster. Thanks Dinesh Pandey -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Gary Stainburn Sent: Friday, April 01, 2005 6:03 PM To: pgsql-sql@postgresql.org Subject: [SQL] Speed up slow select - was gone blind Hi folks. I've got my select working now, but I haven't received the speed increase I'd expected. It replaced an earlier select which combined a single explicit join with multiple froms. The first select is the old one, the second one is the new one (with a new join). The new one takes 24 seconds to run while the old one took 29. How can I redo the select to improve the speed, or what else can I do to optimaise the database? original (ugly) ~ SELECT r.r_id, r.r_registration, r.r_chassis, r.r_vehicle, r.r_fuel, r.r_pack_mats, r.r_delivery, (date(r.r_delivery) - date(now())) AS r_remaining, r.r_created, r.r_completed, r.r_salesman, r.salesman_name, d.d_des, de.de_des, u.u_id, u.u_userid, u.u_username, u.u_salesman, u.u_target, t.t_id, t.t_des, s.s_id, s.s_des, c.c_id, c.c_des, co.com_count, co.com_unseen FROM (SELECT r.r_id, r.r_t_id, r.r_d_id, r.r_de_id, r.r_s_id, r.r_registration, r.r_chassis, r.r_c_id, r.r_vehicle, r.r_fuel, r.r_pack_mats, r.r_delivery, r.r_salesman, r.r_created, r.r_completed, r.r_u_id, u.u_username AS salesman_name FROM (requests r LEFT JOIN users u ON ((r.r_salesman = u.u_id r, users u, request_types t, request_states s, dealerships d, departments de, customers c, comment_tallies co WHERE (r.r_d_id = d.d_id) AND (r.r_s_id = s.s_id) AND (r.r_c_id = c.c_id) AND (r.r_t_id = t.t_id) AND (r.r_d_id = d.d_id) AND (r.r_de_id = de.de_id) AND (r.r_u_id = u.u_id) AND (r.r_id = co.r_id)) ORDER BY r.r_id; new ~~~ SELECT r.r_id, r.r_registration, r.r_chassis, r.r_vehicle, r.r_fuel, r.r_pack_mats, r.r_delivery, (date(r.r_delivery) - date(now())) AS r_remaining, r.r_created, r.r_completed, r.r_salesman, sm.u_username as salesman_name, d.d_des, de.de_des, u.u_id, u.u_userid, u.u_username, u.u_salesman, u.u_target, t.t_id, t.t_des, s.s_id, s.s_des, c.c_id, c.c_des, co.com_count, co.com_unseen, pl.pl_id, pl.pl_desc as plates FROM requests r left outer join users sm on sm.u_id = r.r_salesman left outer join users u on r.r_u_id = u.u_id left outer join request_types t on r.r_t_id = t.t_id left outer join request_states s on r.r_s_id = s.s_id left outer join dealerships d on r.r_d_id = d.d_id left outer join departments de on r.r_de_id = de.de_id left outer join customers c on r.r_c_id = c.c_id left outer join comment_tallies co on r.r_id = co.r_id left outer join plates pl on r.r_plates = pl.pl_id ORDER BY r.r_id; -- Gary Stainburn This email does not contain private or confidential material as it may be snooped on by interested government parties for unknown and undisclosed purposes - Regulation of Investigatory Powers Act, 2000 ---(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 ---(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] Speed up slow select - was gone blind
Can you send the EXPLAIN ANALYZE of each? We can't really tell where the slowdown is without that. On Apr 1, 2005 12:32 PM, Gary Stainburn <[EMAIL PROTECTED]> wrote: > Hi folks. > > I've got my select working now, but I haven't received the speed > increase I'd expected. It replaced an earlier select which combined a > single explicit join with multiple froms. > > The first select is the old one, the second one is the new one (with a > new join). The new one takes 24 seconds to run while the old one took > 29. > > How can I redo the select to improve the speed, or what else can I do to > optimaise the database? > > original (ugly) > ~ > > SELECT r.r_id, r.r_registration, r.r_chassis, r.r_vehicle, r.r_fuel, > r.r_pack_mats, r.r_delivery, > (date(r.r_delivery) - date(now())) AS r_remaining, > r.r_created, r.r_completed, r.r_salesman, r.salesman_name, > d.d_des, de.de_des, > u.u_id, u.u_userid, u.u_username, u.u_salesman, u.u_target, > t.t_id, t.t_des, > s.s_id, s.s_des, > c.c_id, c.c_des, > co.com_count, co.com_unseen > FROM (SELECT r.r_id, r.r_t_id, r.r_d_id, r.r_de_id, r.r_s_id, > r.r_registration, r.r_chassis, r.r_c_id, r.r_vehicle, > r.r_fuel, r.r_pack_mats, r.r_delivery, r.r_salesman, > r.r_created, r.r_completed, r.r_u_id, > u.u_username AS salesman_name > FROM (requests r LEFT JOIN users u ON > ((r.r_salesman = u.u_id r, > users u, > request_types t, > request_states s, > dealerships d, > departments de, > customers c, > comment_tallies co > WHERE (r.r_d_id = d.d_id) AND > (r.r_s_id = s.s_id) AND > (r.r_c_id = c.c_id) AND > (r.r_t_id = t.t_id) AND > (r.r_d_id = d.d_id) AND > (r.r_de_id = de.de_id) AND > (r.r_u_id = u.u_id) AND > (r.r_id = co.r_id)) > ORDER BY r.r_id; > > new > ~~~ > SELECT r.r_id, r.r_registration, r.r_chassis, r.r_vehicle, r.r_fuel, > r.r_pack_mats, r.r_delivery, > (date(r.r_delivery) - date(now())) AS r_remaining, r.r_created, > r.r_completed, r.r_salesman, > sm.u_username as salesman_name, > d.d_des, de.de_des, > u.u_id, u.u_userid, u.u_username, u.u_salesman, u.u_target, > t.t_id, t.t_des, > s.s_id, s.s_des, > c.c_id, c.c_des, > co.com_count, co.com_unseen, > pl.pl_id, pl.pl_desc as plates > FROM requests r > left outer join users sm on sm.u_id = r.r_salesman > left outer join users u on r.r_u_id = u.u_id > left outer join request_types t on r.r_t_id = t.t_id > left outer join request_states s on r.r_s_id = s.s_id > left outer join dealerships d on r.r_d_id = d.d_id > left outer join departments de on r.r_de_id = de.de_id > left outer join customers c on r.r_c_id = c.c_id > left outer join comment_tallies co on r.r_id = co.r_id > left outer join plates pl on r.r_plates = pl.pl_id > ORDER BY r.r_id; > > -- > Gary Stainburn > > This email does not contain private or confidential material as it > may be snooped on by interested government parties for unknown > and undisclosed purposes - Regulation of Investigatory Powers Act, 2000 > > ---(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 > -- Mike Rylander [EMAIL PROTECTED] GPLS -- PINES Development Database Developer http://open-ils.org ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[SQL] Speed up slow select - was gone blind
Hi folks. I've got my select working now, but I haven't received the speed increase I'd expected. It replaced an earlier select which combined a single explicit join with multiple froms. The first select is the old one, the second one is the new one (with a new join). The new one takes 24 seconds to run while the old one took 29. How can I redo the select to improve the speed, or what else can I do to optimaise the database? original (ugly) ~ SELECT r.r_id, r.r_registration, r.r_chassis, r.r_vehicle, r.r_fuel, r.r_pack_mats, r.r_delivery, (date(r.r_delivery) - date(now())) AS r_remaining, r.r_created, r.r_completed, r.r_salesman, r.salesman_name, d.d_des, de.de_des, u.u_id, u.u_userid, u.u_username, u.u_salesman, u.u_target, t.t_id, t.t_des, s.s_id, s.s_des, c.c_id, c.c_des, co.com_count, co.com_unseen FROM (SELECT r.r_id, r.r_t_id, r.r_d_id, r.r_de_id, r.r_s_id, r.r_registration, r.r_chassis, r.r_c_id, r.r_vehicle, r.r_fuel, r.r_pack_mats, r.r_delivery, r.r_salesman, r.r_created, r.r_completed, r.r_u_id, u.u_username AS salesman_name FROM (requests r LEFT JOIN users u ON ((r.r_salesman = u.u_id r, users u, request_types t, request_states s, dealerships d, departments de, customers c, comment_tallies co WHERE (r.r_d_id = d.d_id) AND (r.r_s_id = s.s_id) AND (r.r_c_id = c.c_id) AND (r.r_t_id = t.t_id) AND (r.r_d_id = d.d_id) AND (r.r_de_id = de.de_id) AND (r.r_u_id = u.u_id) AND (r.r_id = co.r_id)) ORDER BY r.r_id; new ~~~ SELECT r.r_id, r.r_registration, r.r_chassis, r.r_vehicle, r.r_fuel, r.r_pack_mats, r.r_delivery, (date(r.r_delivery) - date(now())) AS r_remaining, r.r_created, r.r_completed, r.r_salesman, sm.u_username as salesman_name, d.d_des, de.de_des, u.u_id, u.u_userid, u.u_username, u.u_salesman, u.u_target, t.t_id, t.t_des, s.s_id, s.s_des, c.c_id, c.c_des, co.com_count, co.com_unseen, pl.pl_id, pl.pl_desc as plates FROM requests r left outer join users sm on sm.u_id = r.r_salesman left outer join users u on r.r_u_id = u.u_id left outer join request_types t on r.r_t_id = t.t_id left outer join request_states s on r.r_s_id = s.s_id left outer join dealerships d on r.r_d_id = d.d_id left outer join departments de on r.r_de_id = de.de_id left outer join customers c on r.r_c_id = c.c_id left outer join comment_tallies co on r.r_id = co.r_id left outer join plates pl on r.r_plates = pl.pl_id ORDER BY r.r_id; -- Gary Stainburn This email does not contain private or confidential material as it may be snooped on by interested government parties for unknown and undisclosed purposes - Regulation of Investigatory Powers Act, 2000 ---(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
Re: [SQL] gone blind - can't see syntax error
On Friday 01 April 2005 1:01 pm, you wrote: > On Friday 01 Apr 2005 5:06 pm, Gary Stainburn wrote: > > Hi folks. > > > > I've been looking at this for 10 minutes and can't see what's > > wrong. Anyone care to enlighten me. > > comma after 'r'? I'd only just added that comma, to try to fix it. That shouldn't have been there. The problem was the comma after each of the joins. They should not have been there either. Gary -- Gary Stainburn This email does not contain private or confidential material as it may be snooped on by interested government parties for unknown and undisclosed purposes - Regulation of Investigatory Powers Act, 2000 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] gone blind - can't see syntax error
On Friday 01 Apr 2005 5:06 pm, Gary Stainburn wrote: > Hi folks. > > I've been looking at this for 10 minutes and can't see what's > wrong. Anyone care to enlighten me. comma after 'r'? -- regards kg http://www.livejournal.com/users/lawgon tally ho! http://avsap.sourceforge.net àà à! ---(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] gone blind - can't see syntax error
On Apr 1, 2005 11:36 AM, Gary Stainburn <[EMAIL PROTECTED]> wrote: > Hi folks. > > I've been looking at this for 10 minutes and can't see what's wrong. > Anyone care to enlighten me. > > Thanks > > Gary > > [EMAIL PROTECTED] webroot]# psql -a -f new-view.sql > SELECT r.r_id, r.r_registration, r.r_chassis, r.r_vehicle, r.r_fuel, > r.r_pack_mats, r.r_delivery, > (date(r.r_delivery) - date(now())) AS r_remaining, r.r_created, > r.r_completed, r.r_salesman, > sm.r_salesman as salesman_name, > d.d_des, de.de_des, > u.u_id, u.u_userid, u.u_username, u.u_salesman, u.u_target, > t.t_id, t.t_des, > s.s_id, s.s_des, > c.c_id, c.c_des, > co.com_count, co.com_unseen > FROM requests r, ^^^ > left outer join users sm on sm.u_id = r.r_salesman, > left outer join users u on r.r_u_id = u.u_id, > left outer join request_types t on r.r_t_id = t.t_id, > left outer join request_states s on r.r_s_id = s.s_id, > left outer join dealerships d on r.r_d_id = d.d_id, > left outer join departments de on r.r_de_id = de.de_id, > left outer join customers c on r.r_c_id = c.c_id, > left outer join comment_tallies co on r.r_id = co.r_id > ORDER BY r.r_id; > psql:new-view.sql:19: ERROR: parser: parse error at or near "left" Don't put commas between your joins. -- Mike Rylander [EMAIL PROTECTED] GPLS -- PINES Development Database Developer http://open-ils.org ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] gone blind - can't see syntax error
Try this FROM requests r left outer join users sm on sm.u_id = r.r_salesman left outer join users u on r.r_u_id = u.u_id left outer join request_types t on r.r_t_id = t.t_id left outer join request_states s on r.r_s_id = s.s_id left outer join dealerships d on r.r_d_id = d.d_id left outer join departments de on r.r_de_id = de.de_id left outer join customers c on r.r_c_id = c.c_id left outer join comment_tallies co on r.r_id = co.r_id ORDER BY r.r_id; -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Gary Stainburn Sent: Friday, April 01, 2005 5:06 PM To: pgsql-sql@postgresql.org Subject: [SQL] gone blind - can't see syntax error Hi folks. I've been looking at this for 10 minutes and can't see what's wrong. Anyone care to enlighten me. Thanks Gary [EMAIL PROTECTED] webroot]# psql -a -f new-view.sql SELECT r.r_id, r.r_registration, r.r_chassis, r.r_vehicle, r.r_fuel, r.r_pack_mats, r.r_delivery, (date(r.r_delivery) - date(now())) AS r_remaining, r.r_created, r.r_completed, r.r_salesman, sm.r_salesman as salesman_name, d.d_des, de.de_des, u.u_id, u.u_userid, u.u_username, u.u_salesman, u.u_target, t.t_id, t.t_des, s.s_id, s.s_des, c.c_id, c.c_des, co.com_count, co.com_unseen FROM requests r, left outer join users sm on sm.u_id = r.r_salesman, left outer join users u on r.r_u_id = u.u_id, left outer join request_types t on r.r_t_id = t.t_id, left outer join request_states s on r.r_s_id = s.s_id, left outer join dealerships d on r.r_d_id = d.d_id, left outer join departments de on r.r_de_id = de.de_id, left outer join customers c on r.r_c_id = c.c_id, left outer join comment_tallies co on r.r_id = co.r_id ORDER BY r.r_id; psql:new-view.sql:19: ERROR: parser: parse error at or near "left" -- Gary Stainburn This email does not contain private or confidential material as it may be snooped on by interested government parties for unknown and undisclosed purposes - Regulation of Investigatory Powers Act, 2000 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL] gone blind - can't see syntax error
Hi folks. I've been looking at this for 10 minutes and can't see what's wrong. Anyone care to enlighten me. Thanks Gary [EMAIL PROTECTED] webroot]# psql -a -f new-view.sql SELECT r.r_id, r.r_registration, r.r_chassis, r.r_vehicle, r.r_fuel, r.r_pack_mats, r.r_delivery, (date(r.r_delivery) - date(now())) AS r_remaining, r.r_created, r.r_completed, r.r_salesman, sm.r_salesman as salesman_name, d.d_des, de.de_des, u.u_id, u.u_userid, u.u_username, u.u_salesman, u.u_target, t.t_id, t.t_des, s.s_id, s.s_des, c.c_id, c.c_des, co.com_count, co.com_unseen FROM requests r, left outer join users sm on sm.u_id = r.r_salesman, left outer join users u on r.r_u_id = u.u_id, left outer join request_types t on r.r_t_id = t.t_id, left outer join request_states s on r.r_s_id = s.s_id, left outer join dealerships d on r.r_d_id = d.d_id, left outer join departments de on r.r_de_id = de.de_id, left outer join customers c on r.r_c_id = c.c_id, left outer join comment_tallies co on r.r_id = co.r_id ORDER BY r.r_id; psql:new-view.sql:19: ERROR: parser: parse error at or near "left" -- Gary Stainburn This email does not contain private or confidential material as it may be snooped on by interested government parties for unknown and undisclosed purposes - Regulation of Investigatory Powers Act, 2000 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
