X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 2AEF953790 for ; Wed, 13 Apr 2005 18:08:11 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 83782-03 for ; Wed, 13 Apr 2005 21:07:11 +0000 (GMT) Received: from atlas.jtan.com (atlas.jtan.com [207.106.84.159]) by svr1.postgresql.org (Postfix) with ESMTP id 18E2853DAD for ; Wed, 13 Apr 2005 18:07:10 -0300 (ADT) X-JTAN-Envelope-From: pgsql@tom-mack.com X-JTAN-Envelope-To: pgsql-performance@postgresql.org Received: from [10.100.2.130] (mail.cannontech.com [63.254.39.134]) (authenticated bits=0) by atlas.jtan.com (8.12.8p1/8.12.8) with ESMTP id j3DL72nk028432 (version=TLSv1/SSLv3 cipher=DHE-RSA-AES256-SHA bits=256 verify=NOT); Wed, 13 Apr 2005 17:07:03 -0400 (EDT) Message-ID: <425D89F1.4040907@tom-mack.com> Date: Wed, 13 Apr 2005 16:06:57 -0500 From: Tom Mack User-Agent: Mozilla Thunderbird 1.0.2 (Windows/20050317) X-Accept-Language: en-us, en MIME-Version: 1.0 To: pgsql-performance@postgresql.org Cc: twanger@bluetwanger.de Subject: Problem with slow query (caused by improper nestloop?) Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at hub.org X-Archive-Number: 200504/261 X-Sequence-Number: 11658 Someone (twanger) sent me here from the IRC channel with the following: I have a query that normally takes 0.150 seconds, but after an insert can take 14 seconds. Here's the scenario: Run this query: select * from cad_part left join smart_part using (cannon_part_id) where cad_import_id = 91 order by cad_part_reference_letter, cad_part_id The result is returned in about 150ms. Then I run my import operation which adds 1 new cad_import row, about 30 new cad_part rows, and about 100 new cad_line rows (which aren't involved in the above query). In this case, the new cad_import row has a PK of cad_import_id = 92. When I run the query again (only the where clause changed): select * from cad_part left join smart_part using (cannon_part_id) where cad_import_id = 92 order by cad_part_reference_letter, cad_part_id it takes about 14 seconds (and has a different plan). I can repeat the first query (id=91) and it still executes in 150ms and then repeat the second query and in still takes ~14 seconds. I've found two things that fix this. First, if I run analyze, the second query will take 150ms. Second, if I set enable_nestloop to false the second query will use that same plan that the first does and complete in 150ms. I've posted a bunch of details on my website including the size of the tables (all pretty small), both query plans, and some of the schema. http://tom-mack.com/query_details.html I also just redid the query without the final order by clause with the same results. So I guess my question is, am I doing something wrong? did I miss an index or something? is this a bug (a 100x hit for not running analyze seems a little severe)? should I just run "analyze cad_part" after my inserts to that table? Thanks, --Tom