On Mar 28, 2013 9:07 PM, "kelphet xiong" <kel...@gmail.com> wrote:
> explain analyze select * from inventory;
> QUERY PLAN
>
> --------------------------------------------------------------------------------------------------------------
> ------------
> Seq Scan on inventory (cost=0.00..180937.00 rows=11745000 width=16) (actual time=0.005..1030.403 rows=117450
> 00 loops=1)
> Total runtime: 1750.889 ms
> (2 rows)
A large fraction of that time, if not most is due to timing overhead. You can try the same query without timing by using explain (analyze on, timing off) select * from inventory;
Regards,
Ants Aasma
Thanks a lot for replies from Kevin, Ken, and Ants Aasma. I really aappreciate your suggestions and comments.
My server configuration is two physical quad-core CPUs with hyper-threading enabled.
Each CPU is Intel(R) Xeon(R) CPU E5...@2.40GHz. Physical memory is 16GB.
I set shared_buffers as 4GB, effective_cache_size as 10GB and inventory table is around 500MB.
From the information provided by top command, although the row for postmaster shows that postmaster is using 100%CPU,
the total CPU user time for the whole server never goes beyond 6.6%us.
I guess it is because postgres only uses a single thread to read
the data or “pushing the data around in RAM” according to Kevin’s statement.
Then my question is actually why postgres can not use the remaining 93.4%CPU.
Btw, I also tried the command suggested by Ants Aasma, but got an error:
explain (analyze on, timing off) select * from inventory;
ERROR: syntax error at or near "analyze"
LINE 1: explain (analyze on, timing off) select * from inventory;
^