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.

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.


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

Reply via email to