DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma;
	h=mime-version:in-reply-to:references:date:message-id:subject:from:to
	:cc:content-type:content-transfer-encoding;
	b=jKNQliY3UX3LBdDT4GTm1jM9wZ9hD5iHFKQGRzshNGrR2gRr+PMkywEVyq/6hS13ct
	+P1okT3Kzti+GF9tBYMtaBtJgI2umr7ug/mEQoKkBIc+skPt3lRE2Gly4iORkXfTPQaT
	lwwco4t4V4vS4qe5SnX1QFu+5EDqqx8Ni1hyY=
MIME-Version: 1.0
In-Reply-To: <sig.50826e4e2f.4DA3608E.9090906@samsix.com>
References: <sig.50826e4e2f.4DA3608E.9090906@samsix.com>
Date: Wed, 11 May 2011 12:38:44 -0400
Message-ID: <BANLkTimssX0BS_ycZjye3z7TLiiiPqi8tg@mail.gmail.com>
Subject: Re: Poor performance when joining against inherited tables
From: Robert Haas <robertmhaas@gmail.com>
To: Lucas Madar <madar@samsix.com>
Cc: pgsql-performance@postgresql.org
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable

On Mon, Apr 11, 2011 at 4:11 PM, Lucas Madar <madar@samsix.com> wrote:
> I have a database that contains many tables, each with some common
> characteristics. For legacy reasons, they have to be implemented in a way=
 so
> that they are *all* searchable by an older identifier to find the newer
> identifier. To do this, we've used table inheritance.
>
> Each entry has an id, as well as a legacyid1 and legacyid2. There's a mas=
ter
> table that the application uses, containing a base representation and com=
mon
> characteristics:
>
> objects ( id, ... )
> item ( id, legacyid1, legacyid2 )
> =A0| - itemXX
> =A0| - itemYY
>
> There is nothing at all in the item table, it's just used for inheritance=
.
> However, weird things happen when this table is joined:
>
> EXPLAIN ANALYZE SELECT * FROM objects INNER JOIN item f USING ( id );
>
> =A0QUERY PLAN
> ------------
> =A0Hash Join =A0(cost=3D457943.85..1185186.17 rows=3D8643757 width=3D506)
> =A0 Hash Cond: (f.id =3D objects.id)
> =A0 -> =A0Append =A0(cost=3D0.00..224458.57 rows=3D8643757 width=3D20)
> =A0 =A0 =A0 =A0 -> =A0Seq Scan on item f =A0(cost=3D0.00..26.30 rows=3D16=
30 width=3D20)
> =A0 =A0 =A0 =A0 -> =A0Seq Scan on itemXX f =A0(cost=3D0.00..1.90 rows=3D9=
0 width=3D20)
> =A0 =A0 =A0 =A0 -> =A0Seq Scan on itemYY f =A0(cost=3D0.00..7.66 rows=3D2=
66 width=3D20)
> =A0 =A0 =A0 =A0 -> =A0Seq Scan on itemZZ f =A0(cost=3D0.00..1.02 rows=3D2=
 width=3D20)
> =A0 =A0 =A0 =A0 ...
> =A0 -> =A0Hash =A0(cost=3D158447.49..158447.49 rows=3D3941949 width=3D490=
)
> =A0 =A0 =A0 =A0 -> =A0Seq Scan on objects =A0(cost=3D0.00..158447.49 rows=
=3D3941949
> width=3D490)
>
> This scans everything over everything, and obviously takes forever (there
> are millions of rows in the objects table, and tens of thousands in each
> itemXX table).
>
> However, if I disable seqscan (set enable_seqscan=3Dfalse), I get the
> following plan:
>
> =A0QUERY PLAN
> ------------
> =A0Hash Join =A0(cost=3D10001298843.53..290002337961.71 rows=3D8643757 wi=
dth=3D506)
> =A0 Hash Cond: (f.id =3D objects.id)
> =A0 -> =A0Append =A0(cost=3D10000000000.00..290000536334.43 rows=3D864375=
7 width=3D20)
> =A0 =A0 =A0 =A0 -> =A0Seq Scan on item f =A0(cost=3D10000000000.00..10000=
000026.30
> rows=3D1630 width=3D20)
> =A0 =A0 =A0 =A0 -> =A0Index Scan using xxx_pkey on itemXX f =A0(cost=3D0.=
00..10.60 rows=3D90
> width=3D20)
> =A0 =A0 =A0 =A0 -> =A0Index Scan using yyy_pkey on itemYY f =A0(cost=3D0.=
00..25.24
> rows=3D266 width=3D20)
> =A0 =A0 =A0 =A0 -> =A0Index Scan using zzz_pkey on itemZZ f =A0(cost=3D0.=
00..9.28 rows=3D2
> width=3D20)
> =A0 =A0 =A0 =A0 ...
> =A0 -> =A0Hash =A0(cost=3D999347.17..999347.17 rows=3D3941949 width=3D490=
)
> =A0 =A0 =A0 =A0 -> =A0Index Scan using objects_pkey on objects (cost=3D0.=
00..999347.17
> rows=3D3941949 width=3D490)
>
> This seems like a much more sensible query plan.

I don't think so.  Scanning the index to extract all the rows in a
table is typically going to be a lot slower than a sequential scan.

A more interesting question is why you're not getting a plan like this:

Nested Loop
-> Seq Scan on objects
-> Append
   ->  Index Scan using xxx_pkey on itemXX
   ->  Index Scan using yyy_pkey on itemYY
   ->  Index Scan using zzz_pkey on itemZZ

> But it seems to think doing
> a sequential scan on the *empty* item table is excessively expensive in t=
his
> case.
>
> Aside from enable_seqscan=3Dfalse, is there any way I can make the query
> planner not balk over doing a seqscan on an empty table?

Why would you care?  A sequential scan of an empty table is very fast.

--=20
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company