Skip site navigation (1) Skip section navigation (2)

Re: UNION ALL and sequential scans

From: Mathieu De Zutter <mathieu(at)dezutter(dot)org>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: Brad Jorsch <programmer(at)protech1inc(dot)com>, pgsql-performance(at)postgresql(dot)org
Subject: Re: UNION ALL and sequential scans
Date: 2009-05-14 15:10:29
Message-ID: d4468d970905140810j2b15ce9eq736ef479ae6b02f0@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-performance
On Thu, May 14, 2009 at 4:52 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> "Brad Jorsch" <programmer(at)protech1inc(dot)com> writes:
>>  But if I add a constant-valued column to indicate which branch of the
>>  union each result came from:
>
>>  explain analyze select * from baz join (
>>  select id, val, 'foo'::text as source from foo
>>  union all
>>  select id, val, 'bar'::text as source from bar
>>  ) as foobar on(baz.id2=foobar.id) where baz.id1=42;
>
>>  All of a sudden it insists on a sequential scan (and takes 800 times as
>>  long to run) even when enable_seqscan is set false. Is there a good
>>  reason for this, or is it just a missed opportunity in the optimizer?
>
> It's an ancient and fundamental limitation that is fixed in 8.4.
> Do not expect to see it fixed in 8.3.x.

Does this also apply to the case of a join on an inherited table ?

example: http://archives.postgresql.org/pgsql-performance/2003-10/msg00018.php

Kind regards,

Mathieu

In response to

Responses

pgsql-performance by date

Next:From: Tom LaneDate: 2009-05-14 15:20:33
Subject: Re: UNION ALL and sequential scans
Previous:From: Tom LaneDate: 2009-05-14 14:52:47
Subject: Re: UNION ALL and sequential scans

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group