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