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 C218B3A5A88 for ; Fri, 3 Dec 2004 22:31:22 +0000 (GMT) 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 70697-02 for ; Fri, 3 Dec 2004 22:31:14 +0000 (GMT) Received: from compton.gene.com (fw-open-virt.gene.com [192.12.78.250]) by svr1.postgresql.org (Postfix) with ESMTP id ADF103A5A06 for ; Fri, 3 Dec 2004 22:31:13 +0000 (GMT) Received: from [128.137.187.34] (dhcp187-34.gene.com [128.137.187.34]) by compton.gene.com (Switch-3.1.4/Switch-3.1.0) with ESMTP id iB3MVBuE027983 for ; Fri, 3 Dec 2004 14:31:11 -0800 (PST) Subject: Performance difference in similar queries From: Kiran Mukhyala To: pgsql-performance@postgresql.org Content-Type: text/plain Message-Id: <1102113071.31836.107.camel@scifi.gene.com> Mime-Version: 1.0 X-Mailer: Ximian Evolution 1.4.6 Date: Fri, 03 Dec 2004 14:31:11 -0800 Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests= X-Spam-Level: X-Archive-Number: 200412/88 X-Sequence-Number: 9463 Hi Folks, I have two queries that are of the form : select ... from ... where ... in (list1) AND ... in (list2). The two queries differ only in the size of list2 by 1, but their performances are quite different. Query2 runs much faster than Query1. The queries are: Query 1: SELECT svm,pmodel_id,pseq_id FROM paprospect2 WHERE pseq_id in (8880,10507,10600,10605,10724,10852 ...) AND pmodel_id in (4757,8221,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0); Query 2: SELECT svm,pmodel_id,pseq_id FROM paprospect2 WHERE pseq_id in (8880,10507,10600,10605,10724,10852 ...) AND pmodel_id in (4757,8221,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0); =>Notice the extra zero at the end of query2. The size of list1 is 800 and size of list2 is 49 in case of query1 and 50 in case of query2 The Query Plans are: QUERY PLAN 1: Index Scan using paprospect2_search1, paprospect2_search1, paprospect2_search1, paprospect2_search1, paprospect2_search1, paprospect2_search1, paprospect2_search1, paprospect2_search1 .......... (cost=0.00..10959914.42 rows=45760 width=12) Index Cond: ((pmodel_id = 4757) OR (pmodel_id = 8221) OR (pmodel_id = 0) OR (pmodel_id = 0) OR (pmodel_id = 0) OR (pmodel_id = 0) OR (pmodel_id = 0) OR (pmodel_id = 0) OR ...) Filter: ((pseq_id = 0) OR (pseq_id = 8880) OR (pseq_id = 10507) OR (pseq_id = 10600) OR (pseq_id = 10605) OR ...) QUERY PLAN 2: Index Scan using paprospect2_pseq_id_params_id,paprospect2_pseq_id_params_id, paprospect2_pseq_id_params_id, paprospect2_pseq_id_params_id, paprospect2_pseq_id_params_id, paprospect2_pseq_id_params_id, paprospect2_pseq_id_params_id, paprospect2_pseq_id_params_id, paprospect2_pseq_id_params_id, paprospect2_pseq_id_params_id, paprospect2_pseq_id_params_id, paprospect2_pseq_id_params_id ....... (cost=0.00..11050741.64 rows=46520 width=12) Index Cond: ((pseq_id = 0) OR (pseq_id = 8880) OR (pseq_id = 10507) OR (pseq_id = 10600) OR (pseq_id = 10605) OR (pseq_id = 10724) OR (pseq_id = 10852) OR (pseq_id = 10905) OR (pseq_id = 10945) OR (pseq_id = 10964)....) Filter: ((pmodel_id = 4757) OR (pmodel_id = 8221) OR (pmodel_id = 0) OR (pmodel_id = 0) OR (pmodel_id = 0) OR ...) => Notice that the Index, Index Cond. and Filter are different in the two plans. In short the query plans and performance are quite different although the queries are similar. Can you please explain the difference in performance? Thank you, -Kiran