X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (neptune.hub.org [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id D54DFD1B499 for ; Fri, 2 Jan 2004 06:17:26 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 03672-04 for ; Fri, 2 Jan 2004 02:16:37 -0400 (AST) Received: from pomeray.duluoz.net (12-241-192-70.client.attbi.com [12.241.192.70]) by svr1.postgresql.org (Postfix) with ESMTP id E8D3ED1B4A1 for ; Fri, 2 Jan 2004 02:16:35 -0400 (AST) Received: (qmail 2133 invoked by uid 1000); 2 Jan 2004 06:16:34 -0000 Date: Thu, 1 Jan 2004 22:16:30 -0800 From: Mike Glover To: Tom Lane Cc: pgsql-performance@postgresql.org Subject: Re: Very slow update + not using clustered index Message-Id: <20040101221630.4b001c35.mpg4@duluoz.net> In-Reply-To: <2966.1073016371@sss.pgh.pa.us> References: <20040101193401.6640420b.mpg4@duluoz.net> <2966.1073016371@sss.pgh.pa.us> X-Mailer: Sylpheed version 0.9.0claws (GTK+ 1.2.10; i686-pc-linux-gnu) Mime-Version: 1.0 Content-Type: multipart/signed; protocol="application/pgp-signature"; micalg="pgp-sha1"; boundary="=.:X5DcK3,R.IM5H" X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200401/4 X-Sequence-Number: 5179 --=.:X5DcK3,R.IM5H Content-Type: text/plain; charset=US-ASCII Content-Transfer-Encoding: 7bit Tom- Thanks for the quick response. More details are inline. -mike On Thu, 01 Jan 2004 23:06:11 -0500 Tom Lane wrote: > Mike Glover writes: > AFAICS these plans are identical, and therefore the difference in > runtime must be ascribed to the time spent actually doing the updates. > It seems unlikely that the raw row inserts and updating the single > index could be quite that slow --- perhaps you have a foreign key > or trigger performance problem? There are no foreign keys or triggers for either of the tables. > Is this PG 7.4? Yes, PG 7.4 > > A quick experiment shows that if the planner does not have any reason > to prefer one ordering over another, the current coding will put the > last WHERE clause first: [snip]> > and so you could probably improve matters just by switching the order > of your WHERE clauses. Of course this answer will break as soon as > anyone touches any part of the related code, so I'd like to try to fix > it so that there is actually a principled choice made. Could you send > along the pg_stats rows for these columns? > It looks like the planner is already making a principled choice: bookshelf=> explain select s.* from summary s, inventory i where s.isbn = i.isbn and s.price_min = i.price; QUERY PLAN ----------------------------------------------------------------------- Merge Join (cost=491180.66..512965.72 rows=9237 width=58) Merge Cond: (("outer".price_min = "inner".price) AND ("outer"."?column8?" = "inner"."?column3?")) -> Sort (cost=361887.05..367000.05 rows=2045201 width=58) Sort Key: s.price_min, (s.isbn)::text -> Seq Scan on summary s (cost=0.00..44651.01 rows=2045201 width=58) -> Sort (cost=129293.61..131499.09 rows=882192 width=25) Sort Key: i.price, (i.isbn)::text -> Seq Scan on inventory i (cost=0.00..16173.92 rows=882192 width=25) (8 rows) bookshelf=> explain select s.* from summary s, inventory i where s.price_min = i.price and s.isbn = i.isbn; QUERY PLAN ----------------------------------------------------------------------- Merge Join (cost=491180.66..512965.72 rows=9237 width=58) Merge Cond: (("outer".price_min = "inner".price) AND ("outer"."?column8?" ="inner"."?column3?")) -> Sort (cost=361887.05..367000.05 rows=2045201 width=58) Sort Key: s.price_min, (s.isbn)::text -> Seq Scan on summary s (cost=0.00..44651.01 rows=2045201 width=58) -> Sort(cost=129293.61..131499.09 rows=882192 width=25) Sort Key: i.price, (i.isbn)::text -> Seq Scan on inventory i (cost=0.00..16173.92 rows=882192 width=25) (8 rows) Here are the pg_stats rows: bookshelf=> select * from pg_stats where schemaname='de' and tablename='inventory' and attname='isbn'; schemaname | tablename | attname | null_frac | avg_width | n_distinct | most_common_vals | most_common_freqs | histogram_bounds | correlation ------------+-----------+---------+-----------+-----------+------------ +------------------+-------------------+------------------------------- ----------------------------------------------------------------------- ----------------------+------------- de | inventory | isbn | 0 | 14 | -1 | | | {0002551543,0198268211,0375507299,0486231305,0673395197,0767901576,0810 304430,0865738890,0931595029,1574160052,9971504014} | 1(1 row) bookshelf=> select * from pg_stats where schemaname='de' and tablename='inventory' and attname='price'; schemaname | tablename | attname | null_frac | avg_width | n_distinct | most_common_vals | most_common_freqs | histogram_bounds | correlation ------------+-----------+---------+-----------+-----------+------------ +--------------------------------------------------------------+------- ----------------------------------------------------------------------- -----------------------+----------------------------------------------- --------------------------+------------- de | inventory | price | 0 | 11 | 1628 | {59.95,0.00,54.88,53.30,60.50,64.25,73.63,49.39,50.02,53.37} | {0.259667,0.00633333,0.00533333,0.00466667,0.00466667,0.00466667,0.0046 6667,0.00433333,0.004,0.004} | {49.16,52.06,55.53,59.56,63.78,68.90,76.90,88.53,106.16,143.75,1538.88} | 0.149342(1 row) bookshelf=> select * from pg_stats where schemaname='de' and tablename='summary' and attname='isbn'; schemaname | tablename | attname | null_frac | avg_width | n_distinct | most_common_vals | most_common_freqs | histogram_bounds | correlation ------------+-----------+---------+-----------+-----------+------------ +------------------+-------------------+------------------------------- ----------------------------------------------------------------------- ----------------------+------------- de | summary | isbn | 0 | 14 | -1 | | | {0001984209,020801912X,0395287693,055214911X,0722525915,0787630896,0822 218100,0883856263,1413900275,1843910381,9999955045} | 1(1 row) bookshelf=> select * from pg_stats where schemaname='de' and tablename='summary' and attname='price_min'; schemaname | tablename | attname | null_frac | avg_width | n_distinct | most_common_vals | most_common_freqs | histogram_bounds | correlation ------------+-----------+-----------+-----------+-----------+---------- --+---------------------------------------------------------+---------- ----------------------------------------------------------------------- -------------------+--------------------------------------------------- ------------------+------------- de | summary | price_min | 0 | 10 | 1532 | {0.00,59.95,6.95,6.00,4.07,10.17,11.53,10.85,4.75,8.81} | {0.425333,0.029,0.0193333,0.00533333,0.00333333,0.00333333,0.00333333,0 .003,0.00266667,0.00266667} | {0.05,7.11,10.30,14.28,19.54,27.86,50.47,61.25,76.44,104.79,744.73} | 0.0546667(1 row) (mangled a bit by the auto-linewrap, I'm afraid) > > The second question is: why, oh why does the update take such and > > obscenely long time to complete? > > See above --- the problem is not within the plan, but must be sought > elsewhere. > > regards, tom lane > > ---------------------------(end of > broadcast)--------------------------- TIP 5: Have you checked our > extensive FAQ? > > http://www.postgresql.org/docs/faqs/FAQ.html -- Mike Glover Key ID BFD19F2C --=.:X5DcK3,R.IM5H Content-Type: application/pgp-signature -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.2.3 (GNU/Linux) iD8DBQE/9QzCZrNpxr/RnywRAkdvAJ9IzbSx+3uybk/oivFH/fFKhPmyoACfTmKp M8Ulg4+/e00DFg9+Ev3GsuM= =kKHh -----END PGP SIGNATURE----- --=.:X5DcK3,R.IM5H--