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.