0

I'm trying to measure execution time of a query, but I have a feeling that my results are wrong.

Before every query I execute: sync; echo 3 > /proc/sys/vm/drop_caches

My server log file results are:

2014-02-08 14:28:30 EET LOG:  duration: 32466.103 ms  statement: select * from partsupp
2014-02-08 14:32:48 EET LOG:  duration: 9785.503 ms   statement: select count(*) from partsupp

Shouldn't select count(*) take more time to execute since it makes more operations?

To output all the results from select * I need 4 minutes (not 32 seconds, as indicated by server log). I understand that the client has to output a lot of data and it will be slow, but what about the server's log? Does it count output operations too?

I also used explain analyze and the results are (as expected):

select *:        Total runtime: 13254.733 ms
select count(*): Total runtime: 13463.294 ms

I have run it many times and the results are similar.

What exactly does the log measure?

Why there is so big difference in select * query between explain analyze and server's log, although it doesn't count I/O operations?

What is the difference between log measurement and explain analyze?

I have a dedicated server with Ubuntu 12.04 and PostgreSQL 9.1

Thank you!

user3245803
  • 41
  • 1
  • 3
  • 1
    `count(*)` returns you less information. Most of the the the RDBMS has to do less work for less information. It uses the query plan that delivers the asked-for information with the least amount of work. – usr Feb 08 '14 at 13:31
  • For practical purposes, select count(*) will be faster because it only returns one row to the application calling for the data. – Dan Bracuk Feb 08 '14 at 13:31
  • @DanBracuk True if you're measuring execution time client side and not correcting for that by timing until the *first* row returned, but `explain analyze` reports *server side* execution time. – Craig Ringer Feb 08 '14 at 13:51
  • My results are from server's log file. Clients time is almost 4 minutes. – user3245803 Feb 08 '14 at 14:01
  • This has been tested, thousands of times. – Kermit Feb 08 '14 at 14:05

3 Answers3

2

Any aggregate function has some small overhead - but on second hand SELECT * send to client lot of data in dependency on column numbers and column size.

log measurements is a total query time, it can be similar to EXPLAIN ANALYZE - but much more times is significantly faster, because EXPLAIN ANALYZE collects a execution time (and execution statistics) for all subnodes of execution plan. And it is significant overhead usually. But there are no overhead from transport data from server to client.

Pavel Stehule
  • 42,331
  • 5
  • 91
  • 94
  • To output all the results from select * I need 4 minutes (not 32 seconds, as indicated by server log). I understand that the client has to output a lot of data and it will be slow, but what about the server's log file? Does it include output operations too? – user3245803 Feb 08 '14 at 14:44
  • 1
    No times in log doesn't contains a client side processing. And client side processing of some large data set can take some time (mainly when client is overloaded). It depends on complexity of client side processing. Simple .. for COPY output takes zero time, complex (like tabular) with lot of alignment can be slow. – Pavel Stehule Feb 08 '14 at 15:22
0

The first query asks for all rows in a table. Therefore, the entire table must be read.

The second query only asks for how many rows there are. The database can answer this by reading the entire table, but can also answer this by reading any index it has for that table. Since the index is smaller than the table, doing that would be faster. In practice, nearly all tables have indexes (because a primary key constraint creates an index, too).

meriton
  • 68,356
  • 14
  • 108
  • 175
0

select * = select all data all column included select count(*) = count how many rows for example this table 

------------------------
name | id | address 
----------------------
s    | 12 | abc
---------------------  
x    | 14 | cc
---------------------  
y    | 15 | vv
---------------------  
z   | 16 | ll
---------------------

select * will display all the table

select count(*) will display the total of the rows = 4

Shadi
  • 92
  • 3
  • 1
    I don't think the OP what's to know what the difference between the queries are, but why they have such a different performance –  Feb 08 '14 at 14:05
  • To output all data I need approx. 4 minutes (not 32 sec, as indicated by server's log), but this is client's measurement. I need to know why server's log have such a difference. Does it includes output operation too? – user3245803 Feb 08 '14 at 14:40