Query Processing at backend (I/O on disk) I have a table with 100k rows created on PostgreSQL 9.3

create table demo_bbb
(
  id numeric NOT NULL,
  code_bbb character varying,  
  column_02 character varying,  
  column_03 character varying,  
  column_04 character varying,  
  column_05 character varying,  
  column_06 character varying,  
  column_07 character varying,  
  column_08 character varying,  
  column_09 character varying,  
  column_10 character varying,  
  CONSTRAINT demo_bbb_pk PRIMARY KEY (id)
);


Here's my test (each query is run 3 times):

(1) select id from demo_bbb b ;         -- database query time: ~26ms
(2) select column_10 from demo_bbb b ;  -- database query time: 46ms, 48ms, 51ms
(3) select column_05 from demo_bbb b ;  -- database query time: 37ms, 38 ms, 45ms
(4) select * from demo_bbb b ;          -- database query time: 28ms, 32ms, 37ms


Result: Average time of (4) &lt; time of (2), (3)  (32ms &lt; 45 ms)

Explain analyze:

(1) "Seq Scan on demo_bbb b  (cost=0.00..4425.00 rows=100000 width=6) (actual time=0.008..22.088 rows=100000 loops=1)"
(2) "Seq Scan on demo_bbb b  (cost=0.00..4425.00 rows=100000 width=24) (actual time=0.008..38.702 rows=100000 loops=1)"
(3) "Seq Scan on demo_bbb b  (cost=0.00..4425.00 rows=100000 width=24) (actual time=0.008..32.098 rows=100000 loops=1)"
(4) "Seq Scan on demo_bbb b  (cost=0.00..4425.00 rows=100000 width=232) (actual time=0.007..17.702 rows=100000 loops=1)"


I am curious to know what happened but I can not understand. Could you give me some explanations of how these queries did ?

Note: I traced these queries by using SQL Profiler (download from Enterprisedb)
Pretty much a WAG because you haven't shown EXPLAIN (BUFFERS, OUTPUT) data, but:

select id from demo_bbb b; -- database query time: ~26ms


If you're on 9.2 or newer this probably did an index-only scan on the unique index used to implement the primary key constraint. Given the latter times that's the most likely case; if it'd done a seqscan (as it would on older versions) you'd have got faster results from later queries.

select column_10 from demo_bbb b ;  -- database query time: 46ms, 48ms, 51ms
select column_05 from demo_bbb b ;  -- database query time: 37ms, 38 ms, 45ms
select * from demo_bbb b ;          -- database query time: 28ms, 32ms, 37ms


These are likely to be doing seqscans, and possibly out-of-line TOAST fetching.

The different times will be partly due to caching effects, possibly partly offset by the need to read in more out-of-line TOAST data when you select more columns.

PostgreSQL uses two levels of caching - the operating system's disk cache, and its own shared_buffers. Both are block caches. It does not have a query result cache.

For more information see the PostgreSQL manual and the existing resources about PostgreSQL benchmarking, tuning, caching behaviour, etc.