X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [64.117.224.130]) by svr1.postgresql.org (Postfix) with ESMTP id 2D5B0D1B502 for ; Fri, 26 Sep 2003 07:23:20 +0000 (GMT) Received: from svr1.postgresql.org ([64.117.224.193]) by localhost (neptune.hub.org [64.117.224.130]) (amavisd-new, port 10024) with ESMTP id 47417-08 for ; Fri, 26 Sep 2003 04:23:09 -0300 (ADT) Received: from smtp.pspl.co.in (unknown [202.54.11.65]) by svr1.postgresql.org (Postfix) with ESMTP id 456E7D1B50A for ; Fri, 26 Sep 2003 04:23:07 -0300 (ADT) Received: (from root@localhost) by smtp.pspl.co.in (8.12.9/8.12.9) id h8Q7OQ1O024641 for ; Fri, 26 Sep 2003 12:54:26 +0530 Received: from persistent.co.in (daithan.intranet.pspl.co.in [192.168.7.161]) (authenticated bits=0) by persistent.co.in (8.12.9/8.12.9) with ESMTP id h8Q7OOas024615; Fri, 26 Sep 2003 12:54:25 +0530 Message-ID: <3F73E9DB.80104@persistent.co.in> Date: Fri, 26 Sep 2003 12:55:15 +0530 From: Shridhar Daithankar Organization: Persistent Systems Pvt. Ltd. User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.5b) Gecko/20030917 Thunderbird/0.3a X-Accept-Language: en-us, en MIME-Version: 1.0 To: rantunes@student.dei.uc.pt Cc: pgsql-performance@postgresql.org Subject: Re: Indices arent being used References: <200309252228.h8PMSeXA009897@student.dei.uc.pt> In-Reply-To: <200309252228.h8PMSeXA009897@student.dei.uc.pt> Content-Type: text/plain; charset=us-ascii; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200309/329 X-Sequence-Number: 3695 rantunes@student.dei.uc.pt wrote: > Hi guys > > Im running a Datawarehouse benchmark (APB-1) on PostgreSql. The objective is to > choose which of the to main db (PostgreSQL, MySQL) is fastest. I've run into a > small problem which I hope could be resolved here. > > I'm trying to speed up this query: > > select count(*) from actvars, prodlevel where > actvars.product_level=prodlevel.code_level and > prodlevel.division_level='OY3S5LAPALL6'; > > ACTVARS is a fact table of aproximatly 16 million rows, PRODLEVEL has 20700 > rows. Both have btree indices. > > I executed the query and it took me almost half an hour to execute! Running the > same query on MySQL the result came 6 seconds after. As you can see there is a > large differences between execution times. > > After running an explain: > > Aggregate (cost=3123459.62..3123459.62 rows=1 width=32) > -> Merge Join (cost=3021564.79..3119827.17 rows=1452981 width=32) > Merge Cond: ("outer".product_level = "inner".code_level) > -> Sort (cost=3020875.00..3060938.81 rows=16025523 width=16) > Sort Key: actvars.product_level > -> Seq Scan on actvars (cost=0.00..365711.23 rows=16025523 > width=16) Damn.. Seq. scan for actvars? I would say half an hour is a good throughput. Are there any indexes on both actvars.product_level and prodlevel.code_level? Are they exactly compatible type? int2 and int4 are not compatible in postgresql lingo. That plan should go for index scan. Can you show us the table definitions? And yes, what tuning you did to postgresql? Shridhar