X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 95751DA08B for ; Tue, 4 Oct 2005 17:57:14 -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 79778-03 for ; Tue, 4 Oct 2005 20:57:10 +0000 (GMT) Received: from cluster1.echolabs.net (mail.atlanticbb.net [216.52.118.222]) by svr1.postgresql.org (Postfix) with ESMTP id 1E2E4DA071 for ; Tue, 4 Oct 2005 17:57:10 -0300 (ADT) X-Scanned-By: RAE MPP/Clamd http://www.messagepartners.com X-Scanned-By: RAE MPP/Cloudmark http://www.messagepartners.com Received: from [216.189.176.72] (account jma@atlanticbb.net HELO [127.0.0.1]) by fe2.cluster1.echolabs.net (CommuniGate Pro SMTP 4.3.7) with ESMTPA id 83719658; Tue, 04 Oct 2005 16:57:11 -0400 Message-ID: <4342ECAF.9020903@freedomcircle.net> Date: Tue, 04 Oct 2005 16:57:19 -0400 From: Joe Organization: Freedom Circle, LLC User-Agent: Mozilla Thunderbird 1.0 (Windows/20041206) X-Accept-Language: en-us, en MIME-Version: 1.0 To: PFC Cc: pgsql-performance@postgresql.org Subject: Re: Comparative performance References: <6BCB9D8A16AC4241919521715F4D8BCE92E6D6@algol.sollentuna.se> <433BDB0B.8010002@freedomcircle.net> <433BE1A0.5000807@freedomcircle.net> <4341E328.2060305@freedomcircle.net> In-Reply-To: Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.07 required=5 tests=[AWL=0.020, FORGED_RCVD_HELO=0.05] X-Spam-Level: X-Archive-Number: 200510/51 X-Sequence-Number: 14819 PFC wrote: > - if you use a version before 8, type mismatch will prevent use of the > indexes. I'm using 8.0.3, but the type mismatch between relationship.rel_type and entry_type.type_id was unintended. The current databases use SMALLINT for both. The PostgreSQL schema was derived from an export script stored in Subversion, apparently before the column datatypes were changed. > CREATE INDEX'es ON > entry_type( class_id ) > > relationship( topic_id1, rel_type, topic_id2 ) which becomes your > new PRIMARY KEY > relationship( topic_id2, rel_type, topic_id1 ) Creating the second relationship index was sufficient to modify the query plan to cut down runtime to zero: Sort (cost=75.94..75.95 rows=2 width=381) (actual time=0.000..0.000 rows=0 loops=1) Sort Key: r.rel_type, t.list_name -> Nested Loop (cost=16.00..75.93 rows=2 width=381) (actual time=0.000..0.000 rows=0 loops=1) Join Filter: ((("outer".topic_id1 = "inner".topic_id) AND ("outer".topic_id2 = 1252)) OR (("outer".topic_id2 = "inner".topic_id) AND ("outer".topic_id1 = 1252))) -> Nested Loop (cost=16.00..35.11 rows=1 width=169) (actual time=0.000..0.000 rows=0 loops=1) Join Filter: ("inner".rel_type = "outer".type_id) -> Seq Scan on entry_type e (cost=0.00..18.75 rows=4 width=4) (actual time=0.000..0.000 rows=15 loops=1) Filter: (class_id = 2) -> Materialize (cost=16.00..16.04 rows=4 width=167) (actual time=0.000..0.000 rows=0 loops=15) -> Seq Scan on relationship r (cost=0.00..16.00 rows=4 width=167) (actual time=0.000..0.000 rows=0 loops=1) Filter: ((topic_id2 = 1252) OR (topic_id1 = 1252)) -> Seq Scan on topic t (cost=0.00..30.94 rows=494 width=216) (never executed) Total runtime: 0.000 ms (13 rows) The overall execution time for the Economists page for PostgreSQL is within 4% of the MySQL time, so for the time being I'll leave the query in its current form. Thanks for your help. Joe