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 033C0D1B522 for ; Thu, 25 Sep 2003 22:29:18 +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 73465-08 for ; Thu, 25 Sep 2003 19:28:48 -0300 (ADT) Received: from smtp.dei.uc.pt (smtp.dei.uc.pt [193.137.203.228]) by svr1.postgresql.org (Postfix) with ESMTP id 156B9D1B510 for ; Thu, 25 Sep 2003 19:28:44 -0300 (ADT) Received: from student.dei.uc.pt (student.dei.uc.pt [10.1.0.1]) by smtp.dei.uc.pt (8.11.7/8.11.7) with ESMTP id h8PMTdL12171 for ; Thu, 25 Sep 2003 23:29:39 +0100 Received: from Debug (mail.dei.uc.pt [193.137.203.250]) by student.dei.uc.pt (8.12.8/8.12.8) with SMTP id h8PMSeXA009897 for ; Thu, 25 Sep 2003 23:28:40 +0100 Message-Id: <200309252228.h8PMSeXA009897@student.dei.uc.pt> To: pgsql-performance@postgresql.org From: rantunes@student.dei.uc.pt Subject: Indices arent being used Date: Thu, 25 Sep 2003 22:28:40 GMT X-Mailer: Endymion MailMan Standard Edition v3.0.33 X-UC-DEI-MailScanner-Information: Please contact helpdesk@dei.uc.pt for more information X-UC-DEI-MailScanner: Found to be clean X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200309/325 X-Sequence-Number: 3691 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) -> Sort (cost=689.79..694.48 rows=1877 width=16) Sort Key: prodlevel.code_level -> Seq Scan on prodlevel (cost=0.00..587.75 rows=1877 width=16) Filter: (division_level = 'OY3S5LAPALL6'::bpchar) I found that the indices werent being used. The database has been vacuumed and analyze has been executed. I tried disabling the seqscan, so as to force index usage. The planner uses index scans but the query stil takes a very long time to execute. Any suggestions on resolving this would would be appreciated. P.S: Im running PostgrSQL 7.3.2 --------------------------------------------- This message was sent using Endymion MailMan. http://www.endymion.com/products/mailman/