Обсуждение: Oddity with view

Поиск
Список
Период
Сортировка

Oddity with view

От
Jim 'Decibel!' Nasby
Дата:
Why is this view 9x slower than the base table?

cnuapp_prod@postgres06.nut=# explain analyze select count(*) from
loan_tasks_committed;

QUERY PLAN
------------------------------------------------------------------------
------------------------------------------------------------------
  Aggregate  (cost=994625.69..994625.70 rows=1 width=0) (actual
time=7432.306..7432.306 rows=1 loops=1)
    ->  Seq Scan on loan_tasks_committed  (cost=0.00..929345.35
rows=26112135 width=0) (actual time=0.012..5116.776 rows=26115689
loops=1)
  Total runtime: 7432.360 ms
(3 rows)

Time: 7432.858 ms

loan_tasks effectively does SELECT * FROM loan_tasks_committed UNION
ALL SELECT * FROM loan_tasks_pending;. There's some lookup tables for
_pending, but as this explain shows there's no actual data there
right now.

cnuapp_prod@postgres06.nut=# explain analyze select count(*) from
loan_tasks;

QUERY PLAN
------------------------------------------------------------------------
------------------------------------------------------------------------
---------
  Aggregate  (cost=1516929.75..1516929.76 rows=1 width=0) (actual
time=60396.081..60396.082 rows=1 loops=1)
    ->  Append  (cost=0.00..1190523.94 rows=26112465 width=240)
(actual time=0.023..57902.470 rows=26115689 loops=1)
          ->  Subquery Scan "*SELECT* 1"  (cost=0.00..1190466.70
rows=26112135 width=162) (actual time=0.023..54776.335 rows=26115689
loops=1)
                ->  Seq Scan on loan_tasks_committed
(cost=0.00..929345.35 rows=26112135 width=162) (actual
time=0.014..22531.902 rows=26115689 loops=1)
          ->  Subquery Scan "*SELECT* 2"  (cost=36.10..57.24 rows=330
width=240) (actual time=0.003..0.003 rows=0 loops=1)
                ->  Hash Join  (cost=36.10..53.94 rows=330 width=240)
(actual time=0.002..0.002 rows=0 loops=1)
                      Hash Cond: (ltp.loan_task_code_id = ltc.id)
                      ->  Seq Scan on loan_tasks_pending ltp
(cost=0.00..13.30 rows=330 width=208) (actual time=0.001..0.001
rows=0 loops=1)
                      ->  Hash  (cost=21.60..21.60 rows=1160
width=36) (never executed)
                            ->  Seq Scan on loan_task_codes ltc
(cost=0.00..21.60 rows=1160 width=36) (never executed)
  Total runtime: 60396.174 ms
(11 rows)

Time: 60397.046 ms

  SELECT true AS "committed", loan_tasks_committed.id, ...,
loan_tasks_committed.task_amount
    FROM loan_tasks_committed
UNION ALL
  SELECT false AS "committed", ltp.id, ..., NULL::"unknown" AS
task_amount
    FROM loan_tasks_pending ltp
    JOIN loan_task_codes ltc ON ltp.loan_task_code_id = ltc.id;

The stuff I omitted is just some fields and a few other NULLs. This
is 8.2.9.
--
Decibel! jnasby@cashnetusa.com (512) 569-9461




Re: Oddity with view

От
Tom Lane
Дата:
"Jim 'Decibel!' Nasby" <jnasby@cashnetusa.com> writes:
> loan_tasks effectively does SELECT * FROM loan_tasks_committed UNION
> ALL SELECT * FROM loan_tasks_pending;.

You seem to have neglected to mention a join or two.

            regards, tom lane

Re: Oddity with view

От
Jim 'Decibel!' Nasby
Дата:
On Nov 10, 2008, at 7:06 AM, Tom Lane wrote:
> "Jim 'Decibel!' Nasby" <jnasby@cashnetusa.com> writes:
>> loan_tasks effectively does SELECT * FROM loan_tasks_committed UNION
>> ALL SELECT * FROM loan_tasks_pending;.
>
> You seem to have neglected to mention a join or two.


Yeah, though I did show them at the end of the message...

  SELECT true AS "committed", loan_tasks_committed.id, ...,
loan_tasks_committed.task_amount
    FROM loan_tasks_committed
UNION ALL
  SELECT false AS "committed", ltp.id, ..., NULL::"unknown" AS
task_amount
    FROM loan_tasks_pending ltp
    JOIN loan_task_codes ltc ON ltp.loan_task_code_id = ltc.id;

Thing is, there's no data to be had on that side. All of the time is
going into the seqscan of loan_tasks_committed. But here's what's
really disturbing...

  Aggregate  (cost=994625.69..994625.70 rows=1 width=0) (actual
time=7432.306..7432.306 rows=1 loops=1)
    ->  Seq Scan on loan_tasks_committed  (cost=0.00..929345.35
rows=26112135 width=0) (actual time=0.012..5116.776 rows=26115689
loops=1)

vs

  Aggregate  (cost=1516929.75..1516929.76 rows=1 width=0) (actual
time=60396.081..60396.082 rows=1 loops=1)
    ->  Append  (cost=0.00..1190523.94 rows=26112465 width=240)
(actual time=0.023..57902.470 rows=26115689 loops=1)
          ->  Subquery Scan "*SELECT* 1"  (cost=0.00..1190466.70
rows=26112135 width=162) (actual time=0.023..54776.335 rows=26115689
loops=1)
                ->  Seq Scan on loan_tasks_committed
(cost=0.00..929345.35 rows=26112135 width=162) (actual
time=0.014..22531.902 rows=26115689 loops=1)
          ->  Subquery Scan "*SELECT* 2"  (cost=36.10..57.24 rows=330
width=240) (actual time=0.003..0.003 rows=0 loops=1)

How on earth did the seqscan suddenly take 4x longer? And why is the
subquery scan then doubling the amount of time again?
--
Decibel! jnasby@cashnetusa.com (512) 569-9461




Re: Oddity with view

От
Richard Huxton
Дата:
Jim 'Decibel!' Nasby wrote:
> On Nov 10, 2008, at 7:06 AM, Tom Lane wrote:
>> "Jim 'Decibel!' Nasby" <jnasby@cashnetusa.com> writes:
>>> loan_tasks effectively does SELECT * FROM loan_tasks_committed UNION
>>> ALL SELECT * FROM loan_tasks_pending;.
>>
>> You seem to have neglected to mention a join or two.
>
>
> Yeah, though I did show them at the end of the message...
>
>  SELECT true AS "committed", loan_tasks_committed.id, ...,
> loan_tasks_committed.task_amount
>    FROM loan_tasks_committed
> UNION ALL
>  SELECT false AS "committed", ltp.id, ..., NULL::"unknown" AS task_amount
>    FROM loan_tasks_pending ltp
>    JOIN loan_task_codes ltc ON ltp.loan_task_code_id = ltc.id;
>
> Thing is, there's no data to be had on that side. All of the time is
> going into the seqscan of loan_tasks_committed. But here's what's really
> disturbing...

>    ->  Seq Scan on loan_tasks_committed  (cost=0.00..929345.35
> rows=26112135 width=0) (actual time=0.012..5116.776 rows=26115689 loops=1)

>                ->  Seq Scan on loan_tasks_committed
> (cost=0.00..929345.35 rows=26112135 width=162) (actual
> time=0.014..22531.902 rows=26115689 loops=1)

It's the width - the view is fetching all the rows. Is the "true as
committed" bit confusing it?

--
  Richard Huxton
  Archonet Ltd

Re: Oddity with view

От
Tom Lane
Дата:
"Jim 'Decibel!' Nasby" <jnasby@cashnetusa.com> writes:
> How on earth did the seqscan suddenly take 4x longer? And why is the
> subquery scan then doubling the amount of time again?

Maybe the disk access is less sequential because of the need to fetch
the other table too?

            regards, tom lane

Re: Oddity with view (now with test case)

От
Jim 'Decibel!' Nasby
Дата:
On Nov 10, 2008, at 12:21 PM, Richard Huxton wrote:
> Jim 'Decibel!' Nasby wrote:
>> On Nov 10, 2008, at 7:06 AM, Tom Lane wrote:
>>> "Jim 'Decibel!' Nasby" <jnasby@cashnetusa.com> writes:
>>>> loan_tasks effectively does SELECT * FROM loan_tasks_committed
>>>> UNION
>>>> ALL SELECT * FROM loan_tasks_pending;.
>>>
>>> You seem to have neglected to mention a join or two.
>>
>>
>> Yeah, though I did show them at the end of the message...
>>
>>  SELECT true AS "committed", loan_tasks_committed.id, ...,
>> loan_tasks_committed.task_amount
>>    FROM loan_tasks_committed
>> UNION ALL
>>  SELECT false AS "committed", ltp.id, ..., NULL::"unknown" AS
>> task_amount
>>    FROM loan_tasks_pending ltp
>>    JOIN loan_task_codes ltc ON ltp.loan_task_code_id = ltc.id;
>>
>> Thing is, there's no data to be had on that side. All of the time is
>> going into the seqscan of loan_tasks_committed. But here's what's
>> really
>> disturbing...
>
>>    ->  Seq Scan on loan_tasks_committed  (cost=0.00..929345.35
>> rows=26112135 width=0) (actual time=0.012..5116.776 rows=26115689
>> loops=1)
>
>>                ->  Seq Scan on loan_tasks_committed
>> (cost=0.00..929345.35 rows=26112135 width=162) (actual
>> time=0.014..22531.902 rows=26115689 loops=1)
>
> It's the width - the view is fetching all the rows. Is the "true as
> committed" bit confusing it?

Turns out, no. I was just writing up a stand-alone test case and
forgot to include that, but there's still a big difference (note what
I'm pasting is now from HEAD as of a bit ago, but I see the effect on
8.2 as well):

decibel@platter.local=# explain analyze select count(*) from a;
                                                      QUERY PLAN
------------------------------------------------------------------------
---------------------------------------------
  Aggregate  (cost=137164.57..137164.58 rows=1 width=0) (actual
time=4320.986..4320.986 rows=1 loops=1)
    ->  Seq Scan on a  (cost=0.00..120542.65 rows=6648765 width=0)
(actual time=0.188..2707.433 rows=9999999 loops=1)
  Total runtime: 4321.039 ms
(3 rows)

Time: 4344.158 ms
decibel@platter.local=# explain analyze select count(*) from v;

QUERY PLAN
------------------------------------------------------------------------
------------------------------------------------------------------
  Aggregate  (cost=270286.52..270286.53 rows=1 width=0) (actual
time=14766.630..14766.630 rows=1 loops=1)
    ->  Append  (cost=0.00..187150.20 rows=6650905 width=36) (actual
time=0.039..12810.073 rows=9999999 loops=1)
          ->  Subquery Scan "*SELECT* 1"  (cost=0.00..187030.30
rows=6648765 width=36) (actual time=0.039..10581.367 rows=9999999
loops=1)
                ->  Seq Scan on a  (cost=0.00..120542.65 rows=6648765
width=36) (actual time=0.038..5731.748 rows=9999999 loops=1)
          ->  Subquery Scan "*SELECT* 2"  (cost=37.67..119.90
rows=2140 width=40) (actual time=0.002..0.002 rows=0 loops=1)
                ->  Hash Join  (cost=37.67..98.50 rows=2140 width=40)
(actual time=0.002..0.002 rows=0 loops=1)
                      Hash Cond: (b.c_id = c.c_id)
                      ->  Seq Scan on b  (cost=0.00..31.40 rows=2140
width=8) (actual time=0.000..0.000 rows=0 loops=1)
                      ->  Hash  (cost=22.30..22.30 rows=1230
width=36) (never executed)
                            ->  Seq Scan on c  (cost=0.00..22.30
rows=1230 width=36) (never executed)
  Total runtime: 14766.784 ms
(11 rows)

Time: 14767.550 ms

In 8.2, it took 20 seconds to go through the view:

QUERY PLAN
------------------------------------------------------------------------
------------------------------------------------------------------
  Aggregate  (cost=303960.98..303960.99 rows=1 width=0) (actual
time=20268.877..20268.877 rows=1 loops=1)
    ->  Append  (cost=0.00..211578.98 rows=7390560 width=40) (actual
time=0.038..17112.190 rows=9999999 loops=1)
          ->  Subquery Scan "*SELECT* 1"  (cost=0.00..211467.40
rows=7388620 width=36) (actual time=0.038..13973.782 rows=9999999
loops=1)
                ->  Seq Scan on a  (cost=0.00..137581.20 rows=7388620
width=36) (actual time=0.037..8280.204 rows=9999999 loops=1)
          ->  Subquery Scan "*SELECT* 2"  (cost=36.10..111.58
rows=1940 width=40) (actual time=0.003..0.003 rows=0 loops=1)
                ->  Hash Join  (cost=36.10..92.18 rows=1940 width=40)
(actual time=0.002..0.002 rows=0 loops=1)
                      Hash Cond: (b.c_id = c.c_id)
                      ->  Seq Scan on b  (cost=0.00..29.40 rows=1940
width=8) (actual time=0.000..0.000 rows=0 loops=1)
                      ->  Hash  (cost=21.60..21.60 rows=1160
width=36) (never executed)
                            ->  Seq Scan on c  (cost=0.00..21.60
rows=1160 width=36) (never executed)
  Total runtime: 20269.333 ms
(11 rows)

The results for 8.3 are similar to HEAD.

Here's the commands to generate the test case:

create table a(a int, b text default 'test text');
create table c(c_id serial primary key, c_text text);
insert into c(c_text) values('a'),('b'),('c');
create table b(a int, c_id int references c(c_id));
create view v as select a, b, null as c_id, null as c_text from a
union all select a, null, b.c_id, c_text from b join c on (b.c_id=
c.c_id);
\timing
insert into a(a) select generate_series(1,9999999);
select count(*) from a;
select count(*) from v;
explain analyze select count(*) from a;
explain analyze select count(*) from v;
--
Decibel! jnasby@cashnetusa.com (512) 569-9461




Re: Oddity with view (now with test case)

От
Tom Lane
Дата:
"Jim 'Decibel!' Nasby" <jnasby@cashnetusa.com> writes:
> Here's the commands to generate the test case:

> create table a(a int, b text default 'test text');
> create table c(c_id serial primary key, c_text text);
> insert into c(c_text) values('a'),('b'),('c');
> create table b(a int, c_id int references c(c_id));
> create view v as select a, b, null as c_id, null as c_text from a
> union all select a, null, b.c_id, c_text from b join c on (b.c_id=
> c.c_id);
> \timing
> insert into a(a) select generate_series(1,9999999);
> select count(*) from a;
> select count(*) from v;
> explain analyze select count(*) from a;
> explain analyze select count(*) from v;

I think what you're looking at is projection overhead and per-plan-node
overhead (the EXPLAIN ANALYZE in itself contributes quite a lot of the
latter).  One thing you could do is be more careful about making the
union input types match up so that no subquery scan nodes are required:

create view v2 as select a, b, null::int as c_id, null::text as c_text from a
                           
union all select a, null::text, b.c_id, c_text from b join c on (b.c_id=c.c_id);

On my machine this runs about twice as fast as the original view.

            regards, tom lane

Re: Oddity with view (now with test case)

От
Jim 'Decibel!' Nasby
Дата:
On Nov 10, 2008, at 1:31 PM, Tom Lane wrote:
> "Jim 'Decibel!' Nasby" <jnasby@cashnetusa.com> writes:
>> Here's the commands to generate the test case:
>
>> create table a(a int, b text default 'test text');
>> create table c(c_id serial primary key, c_text text);
>> insert into c(c_text) values('a'),('b'),('c');
>> create table b(a int, c_id int references c(c_id));
>> create view v as select a, b, null as c_id, null as c_text from a
>> union all select a, null, b.c_id, c_text from b join c on (b.c_id=
>> c.c_id);
>> \timing
>> insert into a(a) select generate_series(1,9999999);
>> select count(*) from a;
>> select count(*) from v;
>> explain analyze select count(*) from a;
>> explain analyze select count(*) from v;
>
> I think what you're looking at is projection overhead and per-plan-
> node
> overhead (the EXPLAIN ANALYZE in itself contributes quite a lot of the
> latter).

True... under HEAD explain took 13 seconds while a plain count took
10. Still not very good considering the count from the raw table took
about 4 seconds (with or without explain).

>   One thing you could do is be more careful about making the
> union input types match up so that no subquery scan nodes are
> required:
>
> create view v2 as select a, b, null::int as c_id, null::text as
> c_text from a
> union all select a, null::text, b.c_id, c_text from b join c on
> (b.c_id=c.c_id);
>
> On my machine this runs about twice as fast as the original view.

Am I missing some magic? I'm still getting the subquery scan.

decibel@platter.local=# explain select count(*) from v2;
                                       QUERY PLAN
------------------------------------------------------------------------
--------------
  Aggregate  (cost=279184.19..279184.20 rows=1 width=0)
    ->  Append  (cost=0.00..254178.40 rows=10002315 width=0)
          ->  Subquery Scan "*SELECT* 1"  (cost=0.00..254058.50
rows=10000175 width=0)
                ->  Seq Scan on a  (cost=0.00..154056.75
rows=10000175 width=14)
          ->  Subquery Scan "*SELECT* 2"  (cost=37.67..119.90
rows=2140 width=0)
                ->  Hash Join  (cost=37.67..98.50 rows=2140 width=40)
                      Hash Cond: (b.c_id = c.c_id)
                      ->  Seq Scan on b  (cost=0.00..31.40 rows=2140
width=8)
                      ->  Hash  (cost=22.30..22.30 rows=1230 width=36)
                            ->  Seq Scan on c  (cost=0.00..22.30
rows=1230 width=36)
(10 rows)

Time: 0.735 ms
decibel@platter.local=# \d v2
        View "public.v2"
  Column |  Type   | Modifiers
--------+---------+-----------
  a      | integer |
  b      | text    |
  c_id   | integer |
  c_text | text    |
View definition:
          SELECT a.a, a.b, NULL::integer AS c_id, NULL::text AS c_text
            FROM a
UNION ALL
          SELECT b.a, NULL::text AS b, b.c_id, c.c_text
            FROM b
       JOIN c ON b.c_id = c.c_id;

That's on HEAD, btw.
--
Decibel! jnasby@cashnetusa.com (512) 569-9461




Re: Oddity with view (now with test case)

От
Tom Lane
Дата:
"Jim 'Decibel!' Nasby" <jnasby@cashnetusa.com> writes:
> On Nov 10, 2008, at 1:31 PM, Tom Lane wrote:
>> On my machine this runs about twice as fast as the original view.

> Am I missing some magic? I'm still getting the subquery scan.

Hmm, I'm getting a core dump :-( ... this seems to be busted in HEAD.
8.3 gets it right though.

            regards, tom lane

Re: Oddity with view (now with test case)

От
Jim 'Decibel!' Nasby
Дата:
On Nov 10, 2008, at 9:20 PM, Tom Lane wrote:
> "Jim 'Decibel!' Nasby" <jnasby@cashnetusa.com> writes:
>> On Nov 10, 2008, at 1:31 PM, Tom Lane wrote:
>>> On my machine this runs about twice as fast as the original view.
>
>> Am I missing some magic? I'm still getting the subquery scan.
>
> Hmm, I'm getting a core dump :-( ... this seems to be busted in HEAD.
> 8.3 gets it right though.

Doesn't seem to for me... :/

decibel@platter.local=# select version();

version
------------------------------------------------------------------------
-----------------------------------------------------------------
  PostgreSQL 8.3.5 on i386-apple-darwin8.11.1, compiled by GCC i686-
apple-darwin8-gcc-4.0.1 (GCC) 4.0.1 (Apple Computer, Inc. build 5370)
(1 row)

Time: 0.250 ms
decibel@platter.local=# explain select count(*) from v2;
                                       QUERY PLAN
------------------------------------------------------------------------
--------------
  Aggregate  (cost=279184.19..279184.20 rows=1 width=0)
    ->  Append  (cost=0.00..254178.40 rows=10002315 width=0)
          ->  Subquery Scan "*SELECT* 1"  (cost=0.00..254058.50
rows=10000175 width=0)
                ->  Seq Scan on a  (cost=0.00..154056.75
rows=10000175 width=14)
          ->  Subquery Scan "*SELECT* 2"  (cost=37.67..119.90
rows=2140 width=0)
                ->  Hash Join  (cost=37.67..98.50 rows=2140 width=40)
                      Hash Cond: (b.c_id = c.c_id)
                      ->  Seq Scan on b  (cost=0.00..31.40 rows=2140
width=8)
                      ->  Hash  (cost=22.30..22.30 rows=1230 width=36)
                            ->  Seq Scan on c  (cost=0.00..22.30
rows=1230 width=36)
(10 rows)

Time: 0.923 ms
decibel@platter.local=# \d v2
        View "public.v2"
  Column |  Type   | Modifiers
--------+---------+-----------
  a      | integer |
  b      | text    |
  c_id   | integer |
  c_text | text    |
View definition:
  SELECT a.a, a.b, NULL::integer AS c_id, NULL::text AS c_text
    FROM a
UNION ALL
  SELECT b.a, NULL::text AS b, b.c_id, c.c_text
    FROM b
    JOIN c ON b.c_id = c.c_id;

decibel@platter.local=#
--
Decibel! jnasby@cashnetusa.com (512) 569-9461




Re: Oddity with view (now with test case)

От
Tom Lane
Дата:
"Jim 'Decibel!' Nasby" <jnasby@cashnetusa.com> writes:
> On Nov 10, 2008, at 9:20 PM, Tom Lane wrote:
>> 8.3 gets it right though.

> Doesn't seem to for me... :/

Oh, I was looking at "select * from v2" not "select count(*) from v2".
HEAD is a bit smarter about the latter than 8.3 is.

            regards, tom lane

Re: Oddity with view (now with test case)

От
Jim 'Decibel!' Nasby
Дата:
On Nov 11, 2008, at 1:15 PM, Tom Lane wrote:
> "Jim 'Decibel!' Nasby" <jnasby@cashnetusa.com> writes:
>> On Nov 10, 2008, at 9:20 PM, Tom Lane wrote:
>>> 8.3 gets it right though.
>
>> Doesn't seem to for me... :/
>
> Oh, I was looking at "select * from v2" not "select count(*) from v2".
> HEAD is a bit smarter about the latter than 8.3 is.

So here's something odd... in both 8.3 and HEAD from a while ago it
gives a better plan for SELECT * than for SELECT count(*):

decibel@platter.local=# explain analyze select * from v2;
                                                          QUERY PLAN
------------------------------------------------------------------------
-----------------------------------------------------
  Result  (cost=0.00..254178.40 rows=10002315 width=72) (actual
time=0.049..8452.152 rows=9999999 loops=1)
    ->  Append  (cost=0.00..254178.40 rows=10002315 width=72) (actual
time=0.048..5887.025 rows=9999999 loops=1)
          ->  Seq Scan on a  (cost=0.00..154056.75 rows=10000175
width=14) (actual time=0.048..4207.482 rows=9999999 loops=1)
          ->  Hash Join  (cost=37.67..98.50 rows=2140 width=40)
(actual time=0.002..0.002 rows=0 loops=1)
                Hash Cond: (b.c_id = c.c_id)
                ->  Seq Scan on b  (cost=0.00..31.40 rows=2140
width=8) (actual time=0.000..0.000 rows=0 loops=1)
                ->  Hash  (cost=22.30..22.30 rows=1230 width=36)
(never executed)
                      ->  Seq Scan on c  (cost=0.00..22.30 rows=1230
width=36) (never executed)
  Total runtime: 9494.162 ms
(9 rows)

decibel@platter.local=# explain analyze select count(*) from v2;
                                                                QUERY
PLAN
------------------------------------------------------------------------
-----------------------------------------------------------------
  Aggregate  (cost=279184.19..279184.20 rows=1 width=0) (actual
time=13155.524..13155.524 rows=1 loops=1)
    ->  Append  (cost=0.00..254178.40 rows=10002315 width=0) (actual
time=0.045..11042.562 rows=9999999 loops=1)
          ->  Subquery Scan "*SELECT* 1"  (cost=0.00..254058.50
rows=10000175 width=0) (actual time=0.045..8976.352 rows=9999999
loops=1)
                ->  Seq Scan on a  (cost=0.00..154056.75
rows=10000175 width=14) (actual time=0.045..5936.930 rows=9999999
loops=1)
          ->  Subquery Scan "*SELECT* 2"  (cost=37.67..119.90
rows=2140 width=0) (actual time=0.002..0.002 rows=0 loops=1)
                ->  Hash Join  (cost=37.67..98.50 rows=2140 width=40)
(actual time=0.002..0.002 rows=0 loops=1)
                      Hash Cond: (b.c_id = c.c_id)
                      ->  Seq Scan on b  (cost=0.00..31.40 rows=2140
width=8) (actual time=0.001..0.001 rows=0 loops=1)
                      ->  Hash  (cost=22.30..22.30 rows=1230
width=36) (never executed)
                            ->  Seq Scan on c  (cost=0.00..22.30
rows=1230 width=36) (never executed)
  Total runtime: 13155.642 ms
(11 rows)

decibel@platter.local=# explain analyze select count(*) from (select
* from v2 offset 0) a;
                                                                QUERY
PLAN
------------------------------------------------------------------------
-----------------------------------------------------------------
  Aggregate  (cost=379207.34..379207.35 rows=1 width=0) (actual
time=12592.273..12592.274 rows=1 loops=1)
    ->  Limit  (cost=0.00..254178.40 rows=10002315 width=72) (actual
time=0.173..11057.717 rows=9999999 loops=1)
          ->  Result  (cost=0.00..254178.40 rows=10002315 width=72)
(actual time=0.172..9213.524 rows=9999999 loops=1)
                ->  Append  (cost=0.00..254178.40 rows=10002315
width=72) (actual time=0.172..6608.656 rows=9999999 loops=1)
                      ->  Seq Scan on a  (cost=0.00..154056.75
rows=10000175 width=14) (actual time=0.171..4793.116 rows=9999999
loops=1)
                      ->  Hash Join  (cost=37.67..98.50 rows=2140
width=40) (actual time=0.002..0.002 rows=0 loops=1)
                            Hash Cond: (b.c_id = c.c_id)
                            ->  Seq Scan on b  (cost=0.00..31.40
rows=2140 width=8) (actual time=0.001..0.001 rows=0 loops=1)
                            ->  Hash  (cost=22.30..22.30 rows=1230
width=36) (never executed)
                                  ->  Seq Scan on c
(cost=0.00..22.30 rows=1230 width=36) (never executed)
  Total runtime: 12592.442 ms
(11 rows)

And yes, explain overhead is huge...

decibel@platter.local=# \timing
Timing is on.
decibel@platter.local=# select count(*) from v2;
   count
---------
  9999999
(1 row)

Time: 6217.624 ms
decibel@platter.local=#

--
Decibel! jnasby@cashnetusa.com (512) 569-9461




Re: Oddity with view (now with test case)

От
Tom Lane
Дата:
"Jim 'Decibel!' Nasby" <jnasby@cashnetusa.com> writes:
> So here's something odd... in both 8.3 and HEAD from a while ago it
> gives a better plan for SELECT * than for SELECT count(*):

The short answer is that the Subquery Scan nodes can be dropped out
when they are no-ops, which is to say producing the same set of columns
their input produces (and not testing any filter conditions, but that's
not relevant here).  SELECT count(*) doesn't want to know about any
columns so the output of the UNION arm doesn't match ...

            regards, tom lane