Postgresql – Is the transfer time from PostgreSQL to the application optimisable

postgresql

I have a very simple database running on PostgreSQL 9.3. In this database I have one table called "vehicle_states" created as is:

create table MOBILE_STATE_MESSAGE
(
    id_mobile_state_message SERIAL PRIMARY KEY,
    topic_name VARCHAR(100) NOT NULL,
    date_time TIMESTAMP NOT NULL,

    latitude DOUBLE PRECISION NULL,
    longitude DOUBLE PRECISION NULL,
    altitude REAL NULL,
    depth REAL NULL,
    heading REAL NULL,
    roll REAL NULL,
    pitch REAL NULL,
    speed_along_speed REAL NULL,
    speed_along_direction REAL NULL,
    speed_vector_vx REAL NULL,
    speed_vector_vy REAL NULL
);
CREATE INDEX topic_name_idx on mobile_state_message (topic_name)

So this table is the history of some vehicles. Each vehicles has a different name (the topic_name column). Then, I have an index on the topic names.

This database is populated with data. I have 250000 positions for each vehicles, and 5 vehicles. So I have 1000000 rows in the table.

In pgadmin I run the following query : 

SELECT 
  mobile_state_message.latitude, 
  mobile_state_message.longitude
FROM 
  public.mobile_state_message
WHERE 
  topic_name = 'MyCar';

So, I ask for all the positions of "MyCar" vehicle.
This request took 14 seconds to execute in pgAdmin.

When I start this request in "analyze" mode, with EXPLAIN ANALYZE, I have the following result : 

Bitmap Heap Scan on mobile_state_message  (cost=6448.78..26264.75 rows=251917 width=16) (actual time=28.570..79.810 rows=250000 loops=1)
  Recheck Cond: ((topic_name)::text = 'MyCar'::text)
  ->  Bitmap Index Scan on topic_name_idx  (cost=0.00..6385.80 rows=251917 width=0) (actual time=25.752..25.752 rows=250000 loops=1)
        Index Cond: ((topic_name)::text = 'MyCar'::text)
Total runtime: 87.527 ms

Here I see that the request is processed in 87 ms.

I read some posts on the difference between EXPLAIN ANALYZE and Normal request. They said that the main difference is that when we perform an ANALYZE, there is no result data serialization.

My database server and pgAdmin are running on the same computer, so this can't be a network bandwidth problem.

So can I speed up this serialization time ?

Do you think that 14 seconds to fetch 250000 rows containing 2 double values is a normal timing?

Before posting this message I read the Slow Query Question wiki page. I played with some server parameters like shared_buffers, effective_cache_size, and work_mem, but these parameters didn't speed up anything for my particular problem.

Best Answer

That time is spent by pgAdmin to pack and render data and is not the time spent by Postgres to complete the query execution. Why are you fetching 250.000 rows into pgAdmin? If you need to export the table to a plain-text file (like a CSV with header) you can execute this query:

COPY (
  SELECT 
    mobile_state_message.latitude, 
    mobile_state_message.longitude
  FROM 
    public.mobile_state_message
  WHERE 
    topic_name = 'MyCar'
)
TO '/path/to/file.csv' WITH (FORMAT CSV, HEADER);

Remember that /path/to/ must be owned by postgres user, so run chown -R postgres /path/to/.

This way you can use wathever to analyze the table (if you need to).

Related Question