Hi everyone
We have recently started to port an application from Oracle to PostgreSQL.
So far, we are amazed with how great most things work.
However, we have run into performance problems in one type of query which
is quite common in our application. We have created a (simplified)
reproducible test case which (hopefully!) creates all necessary tables
and data to
show the problem.
Plain-text description of the data model in the test case:
We have a set of objects (like electrical cables), each having
two nodes in the table "connections" (think of these two rows together
as an edge in a graph).
Another table "connections_locked" contains rows for some of
the same objects, which are locked by a long transaction.
The view connections_v performs a union all of the rows from
"connections" which are not modified in the current long
transaction with the rows from "connections_locked" which
are modified in the current long transaction.
Goal:
Given an object id, we want to find all neighbors for this
object (that is, objects which share a node with this object).
Problem:
We think that our query used to find neighbors would benefit
greatly from using some of our indexes, but we fail to make it
do so.
Over to the actual test case:
--
-- Tested on (from select version ()):
-- PostgreSQL 9.0.1 on i686-pc-linux-gnu, compiled by GCC gcc (GCC)
4.1.2 20080704 (Red Hat 4.1.2-46), 32-bit
-- PostgreSQL 9.1beta1 on i686-pc-linux-gnu, compiled by GCC gcc (GCC)
4.1.2 20080704 (Red Hat 4.1.2-46), 32-bit
-- Ubuntu 11.04, uname -a output:
-- Linux 2.6.38-8-generic-pae #42-Ubuntu SMP Mon Apr 11
05:17:09 UTC 2011 i686 i686 i386 GNU/Linux
-- Processor: Intel(R) Core(TM)2 Quad CPU Q9450 @ 2.66GHz
-- Drive: Intel X25-M SSD
drop table if exists connections cascade;
drop table if exists connections_locked cascade;
create table connections (
con_id serial primary key,
locked_by integer not null,
obj_id integer not null,
node integer not null
);
-- create test nodes, two per obj_id
insert into connections (locked_by, obj_id, node)
select 0, n/2, 1000 + (n + 1)/2 from generate_series (1,50) as n;
create index connections_node_idx on connections (node);
create index connections_obj_idx on connections (obj_id);
vacuum analyze connections;
create table connections_locked (
con_id integer not null,
locked_by integer not null,
obj_id integer not null,
node integer not null,
constraint locked_pk primary key (con_id, locked_by)
);
-- mark a few of the objects as locked by a long transaction
insert into connections_locked (con_id, locked_by, obj_id, node)
select n, 1 + n/50, n/2, 1000 + (n + 1)/2 from generate_series (1,25000) as n;
create index connections_locked_node_idx on connections_locked (node);
create index connections_locked_obj_idx on connections_locked (obj_id);
vacuum analyze connections_locked;
-- Create a view showing the world as seen by long transaction 4711.
-- In real life, this uses a session variable instead of a hard-coded value.
create or replace view connections_v as
select * from connections where locked_by <> 4711
union all
select * from connections_locked where locked_by = 4711;
-- This is the query we are trying to optimize.
-- We expect this to be able to use our indexes, but instead get
sequential scans
explain analyze
select
con2.obj_id
from
connections_v con1,
connections_v con2
where
con1.obj_id = 17 and
con2.node = con1.node
;
-- Output:
-- Hash Join (cost=16.69..16368.89 rows=7501 width=4) (actual
time=0.096..778.830 rows=4 loops=1)
-- Hash Cond: ("*SELECT* 1".node = "*SELECT* 1".node)
-- -> Append (cost=0.00..14402.00 rows=500050 width=8) (actual
time=0.011..640.163 rows=50 loops=1)
-- -> Subquery Scan on "*SELECT* 1" (cost=0.00..13953.00
rows=50 width=8) (actual time=0.011..430.645 rows=50 loops=1)
-- -> Seq Scan on connections (cost=0.00..8953.00
rows=50 width=16) (actual time=0.009..178.535 rows=50 loops=1)
-- Filter: (locked_by <> 4711)
-- -> Subquery Scan on "*SELECT* 2" (cost=0.00..449.00
rows=50 width=8) (actual time=3.254..3.254 rows=0 loops=1)
-- -> Seq Scan on connections_locked
(cost=0.00..448.50 rows=50 width=16) (actual time=3.253..3.253 rows=0
loops=1)
-- Filter: (locked_by = 4711)
-- -> Hash (cost=16.66..16.66 rows=3 width=4) (actual
time=0.028..0.028 rows=2 loops=1)
-- Buckets: 1024 Batches: 1 Memory Usage: 1kB
-- -> Append (cost=0.00..16.66 rows=3 width=4) (actual
time=0.013..0.025 rows=2 loops=1)
-- -> Subquery Scan on "*SELECT* 1" (cost=0.00..8.35
rows=2 width=4) (actual time=0.013..0.016 rows=2 loops=1)
-- -> Index Scan using connections_obj_idx on
connections (cost=0.00..8.33 rows=2 width=16) (actual
time=0.012..0.014 rows=2 loops=1)
-- Index Cond: (obj_id = 17)
--
