On 01/23/2012 07:10 PM, Adrian Klaver wrote: > On Monday, January 23, 2012 7:32:35 am Sim Zacks wrote: >> On 01/23/2012 05:13 PM, Adrian Klaver wrote: > >> >> When I throw in code to make the select only return the correct rows >> The select statement takes 9 secs by itself: >> select a.partid,a.deliverywks >> from poparts a where popartid in ( >> select b.popartid from poparts b >> join pos c using(poid) >> join stock.lastrfqdateperpart d using(partid) >> where c.isrfq and c.issuedate > d.issuedate-7 >> AND b.unitprice > 0::numeric AND b.quantity >= 100::numeric AND >> c.postatusid = ANY (ARRAY[40, 41]) >> and b.partid=a.partid >> order by b.partid,b.unitprice, b.deliverywks >> limit 1 >> ) > > To clarify what I posted earlier, my suggestion was based on rewriting the > second query as: > > select b.partid,b.deliverywks b.popartid from poparts b > join pos c using(poid) > join stock.lastrfqdateperpart d using(partid) > where c.isrfq and c.issuedate > d.issuedate-7 > AND b.unitprice > 0::numeric AND b.quantity >= 100::numeric AND > c.postatusid = ANY (ARRAY[40, 41]) > order by b.partid,b.unitprice, b.deliverywks > limit 1 > > I may be missing the intent of your original query, but I think the above > gets > to the same result without the IN. >
My first query returns all rows of each part ordered such so that the row I want to actually update the table with is last. This query returns 12000 rows, for the 600 parts I want to update. My second query with the limit within the subselect gets 1 row per part. This returns 600 rows, 1 row for each part I want to update. Your suggestion would only return one row. See http://www.pgsql.cz/index.php/PostgreSQL_SQL_Tricks#Select_first_n_rows_from_group for reference. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
