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


---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

Reply via email to