Re: explain plan visibility

Hi Laurenz:

Thank you for your kind reply.

Please let me dig it a little more:

I think that when a session is accessing a postgersql table. It will be
influenced by the followings:

Really old data (needed to be vacuumed, eg: old image at one day ago).

Recent data (committed and uncommitted), because they are all in the data
block.

Isn’t it strange that I have to access my data among somebody’s un-decided
data?

How if there is a busy system having a table accessed by so many sessions
at same time？

They will all be slowed down because of uncommitted but flushed out data, I
think.

I hope in future the architecture of PostgreSQL can put the committed data
& uncommitted data apart,

Or even put them in separate physical disks.That will Help to improve
performance I think.

Jian Gao

2012/11/9 Albe Laurenz <laurenz(dot)albe(at)wien(dot)gv(dot)at>

> 高健 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
>