DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.com;
	h=Message-ID:X-YMail-OSG:Received:X-Mailer:References:Date:From:Reply-To:Subject:To:In-Reply-To:MIME-Version:Content-Type:Content-Transfer-Encoding;
	b=rdu6yLzHFDKh1hMeQ1X38SC2+N5RY8zs4BR3KoKm/lqAiGj8oSXu9WAHdjE/YaSX9R9dbvBqKseN1Ylmn1ecrq9nYKs3H963nTJ9d00nlADRXOSueX4cYKHgtQqgk85Mmtys82O3YjavfQXnJc6h86dKgQuFvum6zA5kIZrwKDc=;
Message-ID: <914709.17135.qm@web112411.mail.gq1.yahoo.com>
References: <BANLkTikgkPCuq1_KcnpAP7JAGUM32c0sMQ@mail.gmail.com>
Date: Fri, 13 May 2011 07:23:19 -0700 (PDT)
From: Denis de Bernardy <ddebernardy@yahoo.com>
Reply-To: Denis de Bernardy <ddebernardy@yahoo.com>
Subject: Re: How to avoid seq scans for joins between union-all views (test
	case included)
To: Fredrik Widlert <fredrik.widlert@digpro.se>,
	"pgsql-performance@postgresql.org" <pgsql-performance@postgresql.org>
In-Reply-To: <BANLkTikgkPCuq1_KcnpAP7JAGUM32c0sMQ@mail.gmail.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=iso-8859-1
Content-Transfer-Encoding: quoted-printable

I might have misread, but:=0A=0A> select * from connections where locked_by=
 <> 4711=0A> union all=0A> select * from connections_locked where locked_by=
 =3D 4711;=0A=0A=0AThe first part will result in a seq scan irrespective of=
 indexes, and the second has no index on locked_by. The best you can do is =
to eliminate the seq scan on the second by adding the missing index on lock=
ed_by.=0A=0AThat said, note that index usage depends on your data distribut=
ion: postgres may identify that it'll read most/all of the table anyway, an=
d opt to do a (cheaper) seq scan instead.=0A=0AD=0A=0A=0A----- Original Mes=
sage -----=0A> From: Fredrik Widlert <fredrik.widlert@digpro.se>=0A> To: pg=
sql-performance@postgresql.org=0A> Cc: =0A> Sent: Friday, May 13, 2011 1:55=
 PM=0A> Subject: [PERFORM] How to avoid seq scans for joins between union-a=
ll views (test case included)=0A> =0A> Hi everyone=0A> =0A> We have recentl=
y started to port an application from Oracle to PostgreSQL.=0A> So far, we =
are amazed with how great most things work.=0A> =0A> However, we have run i=
nto performance problems in one type of query which=0A> is quite common in =
our application. We have created a (simplified)=0A> reproducible test case =
which (hopefully!) creates all necessary tables=0A> and data to=0A> show th=
e problem.=0A> =0A> Plain-text description of the data model in the test ca=
se:=0A> =0A> We have a set of objects (like electrical cables), each having=
=0A> two nodes in the table "connections" (think of these two rows together=
=0A> as an edge in a graph).=0A> =0A> Another table "connections_locked" co=
ntains rows for some of=0A> the same objects, which are locked by a long tr=
ansaction.=0A> =0A> The view connections_v performs a union all of the rows=
 from=0A> "connections" which are not modified in the current long=0A> tran=
saction with the rows from "connections_locked" which=0A> are modified in t=
he current long transaction.=0A> =0A> Goal:=0A> Given an object id, we want=
 to find all neighbors for this=0A> object (that is, objects which share a =
node with this object).=0A> =0A> Problem:=0A> We think that our query used =
to find neighbors would benefit=0A> greatly from using some of our indexes,=
 but we fail to make it=0A> do so.=0A> =0A> =0A> Over to the actual test ca=
se:=0A> =0A> ----------------------------------------------=0A> =0A> -- Tes=
ted on (from select version ()):=0A> -- PostgreSQL 9.0.1 on i686-pc-linux-g=
nu, compiled by GCC gcc (GCC)=0A> 4.1.2 20080704 (Red Hat 4.1.2-46), 32-bit=
=0A> -- PostgreSQL 9.1beta1 on i686-pc-linux-gnu, compiled by GCC gcc (GCC)=
=0A> 4.1.2 20080704 (Red Hat 4.1.2-46), 32-bit=0A> =0A> -- Ubuntu 11.04, un=
ame -a output:=0A> -- Linux <hostname> 2.6.38-8-generic-pae #42-Ubuntu SMP =
Mon Apr 11=0A> 05:17:09 UTC 2011 i686 i686 i386 GNU/Linux=0A> -- Processor:=
 Intel(R) Core(TM)2 Quad=A0 CPU=A0  Q9450=A0 @ 2.66GHz=0A> -- Drive: Intel =
X25-M SSD=0A> =0A> =0A> drop table if exists connections cascade;=0A> drop =
table if exists connections_locked cascade;=0A> =0A> =0A> create table conn=
ections (=0A> =A0 con_id serial primary key,=0A> =A0 locked_by integer not =
null,=0A> =A0 obj_id integer not null,=0A> =A0 node integer not null=0A> );=
=0A> =0A> =0A> -- create test nodes, two per obj_id=0A> insert into connect=
ions (locked_by, obj_id, node)=0A> select 0, n/2, 1000 + (n + 1)/2 from gen=
erate_series (1,500000) as n;=0A> =0A> create index connections_node_idx on=
 connections (node);=0A> create index connections_obj_idx on connections (o=
bj_id);=0A> vacuum analyze connections;=0A> =0A> =0A> =0A> create table con=
nections_locked (=0A> =A0 con_id integer not null,=0A> =A0 locked_by intege=
r not null,=0A> =A0 obj_id integer not null,=0A> =A0 node integer not null,=
=0A> =A0 constraint locked_pk primary key (con_id, locked_by)=0A> );=0A> =
=0A> -- mark a few of the objects as locked by a long transaction=0A> inser=
t into connections_locked (con_id, locked_by, obj_id, node)=0A> select n, 1=
 + n/50, n/2, 1000 + (n + 1)/2 from generate_series (1,25000) as n;=0A> =0A=
> create index connections_locked_node_idx on connections_locked (node);=0A=
> create index connections_locked_obj_idx on connections_locked (obj_id);=
=0A> vacuum analyze connections_locked;=0A> =0A> =0A> -- Create a view show=
ing the world as seen by long transaction 4711.=0A> -- In real life, this u=
ses a session variable instead of a hard-coded value.=0A> create or replace=
 view connections_v as=0A> select * from connections where locked_by <> 471=
1=0A> union all=0A> select * from connections_locked where locked_by =3D 47=
11;=0A> =0A> =0A> -- This is the query we are trying to optimize.=0A> -- We=
 expect this to be able to use our indexes, but instead get=0A> sequential =
scans=0A> explain analyze=0A> select=0A> =A0 =A0  con2.obj_id=0A> from=0A> =
=A0 =A0  connections_v con1,=0A> =A0 =A0  connections_v con2=0A> where=0A> =
=A0 =A0  con1.obj_id =3D 17 and=0A> =A0 =A0  con2.node =3D con1.node=0A> ;=
=0A> =0A> =0A> -- Output:=0A> -- Hash Join=A0 (cost=3D16.69..16368.89 rows=
=3D7501 width=3D4) (actual=0A> time=3D0.096..778.830 rows=3D4 loops=3D1)=0A=
> --=A0  Hash Cond: ("*SELECT* 1".node =3D "*SELECT* 1".node)=0A> --=A0  ->=
=A0 Append=A0 (cost=3D0.00..14402.00 rows=3D500050 width=3D8) (actual=0A> t=
ime=3D0.011..640.163 rows=3D500000 loops=3D1)=0A> --=A0 =A0 =A0 =A0  ->=A0 =
Subquery Scan on "*SELECT* 1"=A0 (cost=3D0.00..13953.00=0A> rows=3D500000 w=
idth=3D8) (actual time=3D0.011..430.645 rows=3D500000 loops=3D1)=0A> --=A0 =
=A0 =A0 =A0 =A0 =A0 =A0  ->=A0 Seq Scan on connections=A0 (cost=3D0.00..895=
3.00=0A> rows=3D500000 width=3D16) (actual time=3D0.009..178.535 rows=3D500=
000 loops=3D1)=0A> --=A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0  Filter: (lock=
ed_by <> 4711)=0A> --=A0 =A0 =A0 =A0  ->=A0 Subquery Scan on "*SELECT* 2"=
=A0 (cost=3D0.00..449.00=0A> rows=3D50 width=3D8) (actual time=3D3.254..3.2=
54 rows=3D0 loops=3D1)=0A> --=A0 =A0 =A0 =A0 =A0 =A0 =A0  ->=A0 Seq Scan on=
 connections_locked=0A> (cost=3D0.00..448.50 rows=3D50 width=3D16) (actual =
time=3D3.253..3.253 rows=3D0=0A> loops=3D1)=0A> --=A0 =A0 =A0 =A0 =A0 =A0 =
=A0 =A0 =A0 =A0  Filter: (locked_by =3D 4711)=0A> --=A0  ->=A0 Hash=A0 (cos=
t=3D16.66..16.66 rows=3D3 width=3D4) (actual=0A> time=3D0.028..0.028 rows=
=3D2 loops=3D1)=0A> --=A0 =A0 =A0 =A0  Buckets: 1024=A0 Batches: 1=A0 Memor=
y Usage: 1kB=0A> --=A0 =A0 =A0 =A0  ->=A0 Append=A0 (cost=3D0.00..16.66 row=
s=3D3 width=3D4) (actual=0A> time=3D0.013..0.025 rows=3D2 loops=3D1)=0A> --=
=A0 =A0 =A0 =A0 =A0 =A0 =A0  ->=A0 Subquery Scan on "*SELECT* 1"=A0 =0A> (c=
ost=3D0.00..8.35=0A> rows=3D2 width=3D4) (actual time=3D0.013..0.016 rows=
=3D2 loops=3D1)=0A> --=A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0  ->=A0 Index =
Scan using connections_obj_idx on=0A> connections=A0 (cost=3D0.00..8.33 row=
s=3D2 width=3D16) (actual=0A> time=3D0.012..0.014 rows=3D2 loops=3D1)=0A> -=
-=A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0  Index Cond: (obj_id =
=3D 17)=0A> --=A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0  Filter: =
(locked_by <> 4711)=0A> --=A0 =A0 =A0 =A0 =A0 =A0 =A0  ->=A0 Subquery Scan =
on "*SELECT* 2"=A0 =0A> (cost=3D0.00..8.30=0A> rows=3D1 width=3D4) (actual =
time=3D0.008..0.008 rows=3D0 loops=3D1)=0A> --=A0 =A0 =A0 =A0 =A0 =A0 =A0 =
=A0 =A0 =A0  ->=A0 Index Scan using connections_locked_obj_idx=0A> on conne=
ctions_locked=A0 (cost=3D0.00..8.29 rows=3D1 width=3D16) (actual=0A> time=
=3D0.007..0.007 rows=3D0 loops=3D1)=0A> --=A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =
=A0 =A0 =A0 =A0 =A0  Index Cond: (obj_id =3D 17)=0A> --=A0 =A0 =A0 =A0 =A0 =
=A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0  Filter: (locked_by =3D 4711)=0A> =0A> =0A>=
 =0A> -- Rewriting the query to an almost-equivalent form yields almost the=
=0A> same result (that is, seq scans)=0A> explain analyze=0A> select=0A> =
=A0 =A0 con2.obj_id=0A> from=0A> =A0  connections_v con2=0A> =A0  where con=
2.node in (select node from connections_v con1 where=0A> con1.obj_id =3D 17=
);=0A> =0A> =0A> -- Simplifying the query even more to use a sub-select wit=
h a=0A> hard-coded value still results in seq scans=0A> explain analyze=0A>=
 select=0A> =A0 =A0 con2.obj_id=0A> from=0A> =A0  connections_v con2=0A> =
=A0  where con2.node in (select 1015);=0A> =0A> =0A> -- Finally, when we si=
mplify even more and just use a constant, we get=0A> the index accesses we =
were hoping=0A> -- for all along.=0A> explain analyze=0A> select=0A> =A0 =
=A0 con2.obj_id=0A> from=0A> =A0  connections_v con2=0A> =A0  where con2.no=
de in (1015);=0A> =0A> -- Result=A0 (cost=3D0.00..16.66 rows=3D3 width=3D4)=
 (actual time=3D0.048..0.079=0A> rows=3D2 loops=3D1)=0A> --=A0  ->=A0 Appen=
d=A0 (cost=3D0.00..16.66 rows=3D3 width=3D4) (actual=0A> time=3D0.047..0.07=
6 rows=3D2 loops=3D1)=0A> --=A0 =A0 =A0 =A0  ->=A0 Subquery Scan on "*SELEC=
T* 1"=A0 (cost=3D0.00..8.35 =0A> rows=3D2=0A> width=3D4) (actual time=3D0.0=
46..0.049 rows=3D2 loops=3D1)=0A> --=A0 =A0 =A0 =A0 =A0 =A0 =A0  ->=A0 Inde=
x Scan using connections_node_idx on=0A> connections=A0 (cost=3D0.00..8.33 =
rows=3D2 width=3D16) (actual=0A> time=3D0.046..0.048 rows=3D2 loops=3D1)=0A=
> --=A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0  Index Cond: (node =3D 1015)=0A=
> --=A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0  Filter: (locked_by <> 4711)=0A=
> --=A0 =A0 =A0 =A0  ->=A0 Subquery Scan on "*SELECT* 2"=A0 (cost=3D0.00..8=
.30 =0A> rows=3D1=0A> width=3D4) (actual time=3D0.025..0.025 rows=3D0 loops=
=3D1)=0A> --=A0 =A0 =A0 =A0 =A0 =A0 =A0  ->=A0 Index Scan using connections=
_locked_node_idx on=0A> connections_locked=A0 (cost=3D0.00..8.29 rows=3D1 w=
idth=3D16) (actual=0A> time=3D0.024..0.024 rows=3D0 loops=3D1)=0A> --=A0 =
=A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0  Index Cond: (node =3D 1015)=0A> --=A0 =
=A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0  Filter: (locked_by =3D 4711)=0A> =0A> =
=0A> =0A> ------- end of test case -----=0A> =0A> Can someone explain what =
is happening here? Is there some way we can=0A> rewrite our query or some s=
etting we could turn on or off to get the=0A> optimizer to choose to use ou=
r indexes?=0A> =0A> (testing with "set enable_seqscan =3D false;" does not =
make a difference=0A> as far as we can see)=0A> =0A> To verify that we have=
 really created all necessary indexes, we have=0A> converted this simplifie=
d test case to Oracle syntax and tested it on=0A> our Oracle server. In thi=
s case, we do get the expected index accesses,=0A> so we think that we have=
 in fact managed to isolate the problem using=0A> this test case.=0A> =0A> =
What we are hoping for:=0A> Since we have lots of queries joining these kin=
d of "union all"-views=0A> between a master table and a transaction table, =
we would be really=0A> glad to hear something like "when you use these kind=
s of views, you=0A> need to do X, Y and Z to get good performance".=0A> =0A=
> Thanks in advance for any help!=0A> /Fredrik=0A> =0A> -- =0A> Sent via pg=
sql-performance mailing list (pgsql-performance@postgresql.org)=0A> To make=
 changes to your subscription:=0A> http://www.postgresql.org/mailpref/pgsql=
-performance=0A>