Re: Comparative performance

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