Re: explain plan visibility

高健  wrote:
> 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)

"SELECT count(*) FROM ptest" may be simple, but it is expensive
because it has to visit every tuple in the table.
The first time you run it after the insert it might also trigger
considerable write activity (hint bits), but that on the side.

If you need only an estimate, try
SELECT sum(reltuples) FROM pg_class WHERE relname IN ('ptest', 'ctest01', 'ctest02');

Yours,
Laurenz Albe