I have a table that holds bidirectional links between objects, and had
foolishly assumed that a view I created to simplify access would be
rewritten to use the same indexes as the long version. Today I noticed
things were rather slow, and I was disappointed to find out it wasn't
working as I had expected:
Here's a simplified case. The original query:
ita_devel=> EXPLAIN SELECT seq FROM links WHERE id1 = 84 AND id1_type = 'pers'
ita_devel-> UNION SELECT seq FROM links WHERE id2 = 84 AND id2_type = 'pers';
NOTICE: QUERY PLAN:
Unique (cost=6.06..6.07 rows=1 width=4)
-> Sort (cost=6.06..6.06 rows=2 width=4)
-> Append (cost=0.00..6.05 rows=2 width=4)
-> Subquery Scan *SELECT* 1 (cost=0.00..3.02 rows=1 width=4)
-> Index Scan using links_id1 on links (cost=0.00..3.02 rows=1 width=4)
-> Subquery Scan *SELECT* 2 (cost=0.00..3.03 rows=1 width=4)
-> Index Scan using links_id2 on links (cost=0.00..3.03 rows=1 width=4)
EXPLAIN
Now in order to avoid repeating that UNION all over the place, I tried
this view:
ita_devel=> CREATE VIEW flat AS
ita_devel-> SELECT seq, id1 AS from_id, id1_type AS from_type,
ita_devel-> id2 AS to_id, id2_type AS to_type FROM links
ita_devel-> UNION SELECT seq, id2 AS from_id, id2_type AS from_type,
ita_devel-> id1 AS to_id, id1_type AS to_type FROM links;
CREATE
ita_devel=> EXPLAIN SELECT seq FROM flat WHERE from_id = 84 AND from_type = 'pers';
NOTICE: QUERY PLAN:
Subquery Scan flat (cost=41.18..48.58 rows=59 width=36)
-> Unique (cost=41.18..48.58 rows=59 width=36)
-> Sort (cost=41.18..41.18 rows=592 width=36)
-> Append (cost=0.00..13.92 rows=592 width=36)
-> Subquery Scan *SELECT* 1 (cost=0.00..6.96 rows=296 width=36)
-> Seq Scan on links (cost=0.00..6.96 rows=296 width=36)
-> Subquery Scan *SELECT* 2 (cost=0.00..6.96 rows=296 width=36)
-> Seq Scan on links (cost=0.00..6.96 rows=296 width=36)
EXPLAIN
The result is the same, but no more index scan. There are very few
matching records in the table, so this has a real performance impact.
I guess maybe I'm expecting too much magic optimization. Is this
something it should be able to figure out?
--
Christopher Masto Senior Network Monkey NetMonger Communications
chris@netmonger.net info@netmonger.net http://www.netmonger.net
Free yourself, free your machine, free the daemon -- http://www.freebsd.org/
