explain plan visibility

From: 高健 <luckyjackgao(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: explain plan visibility
Date: 2012-11-09 10:01:40
Message-ID: CAL454F0iek6m4JuGUw+x=uQ7OVN=-_4zw0_NSCq-4=+U921J6w@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hi all:

I have one question about the visibility of explain plan.

Firstly , I was inserting into data to a table. I use : [ insert into
ptest select * from test02; ]

And test02 table has 10,000,000 records. And ptest is a parent table,
which has two distribution child table --- ctest01 and ctest02.

When I execute the above sql statement, it takes some time to execute
because of data volume.

Before the above sql statement finish, I open another session with psql,
and execute: [ select count(*) from ptest; ]
Because the insert into statement in other session has not finished, I got
the result of zero.

Before first session finish, If I check the explain of select, I got:
postgres=# explain select count(*) from ptest;
QUERY PLAN
-----------------------------------------------------------------------------------
Aggregate (cost=55406.40..55406.41 rows=1 width=0)
-> Append (cost=0.00..49601.92 rows=2321793 width=0)
-> Seq Scan on ptest (cost=0.00..0.00 rows=1 width=0)
-> Seq Scan on ctest01 ptest (cost=0.00..24776.52 rows=1159752
width=0)
-> Seq Scan on ctest02 ptest (cost=0.00..24825.40 rows=1162040
width=0)
(5 rows)postgres=#

I think that is because postgresql is holding commited and uncommited
data together physically(vacuum is needed because of it?).

Is there some method that allow simple select such as select count(*) do
not activate the explain plan ?
(I think the more table is bigger, the more sample data is needed)

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Achilleas Mantzios 2012-11-09 10:17:14 Re: Memory issue on FreeBSD
Previous Message Frank Broniewski 2012-11-09 08:37:14 Re: Memory issue on FreeBSD