From: Joe <svn@freedomcircle.net>
To: PFC <lists@boutiquenumerique.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Comparative performance
Date: Tue, 04 Oct 2005 16:57:19 -0400
Message-ID: <4342ECAF.9020903@freedomcircle.net> (raw)
In-Reply-To: <op.sx349gxcth1vuj@localhost>
References: <6BCB9D8A16AC4241919521715F4D8BCE92E6D6@algol.sollentuna.se>
<433BDB0B.8010002@freedomcircle.net>
<Pine.LNX.4.58.0509292230310.26600@linuxworld.com.au>
<433BE1A0.5000807@freedomcircle.net>
<op.sxvguropth1vuj@localhost>
<4341E328.2060305@freedomcircle.net>
<op.sx349gxcth1vuj@localhost>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: svn@freedomcircle.net, lists@boutiquenumerique.com
Subject: Re: Comparative performance
In-Reply-To: <4342ECAF.9020903@freedomcircle.net>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox