2 deleted 4 characters in body; edited title
source | link

Does Is the transfer time from PostgreSQL to the application is optimisable?

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 datasdata. I have 250000 positions for each vehicles, and 5 vehicles. So I have 1000000 rows in the table.

In pgadmin i launchI 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

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 ANAYLZEANALYZE, there is no result data serialization.

My database server and PGAdmingpgAdmin are running on the same computer, so this can't be a network bandwithbandwidth 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.

Does transfer time from PostgreSQL to application is optimisable?

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 datas. I have 250000 positions for each vehicles, and 5 vehicles. So I have 1000000 rows in the table.

In pgadmin i launch 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 ANAYLZE, there is no result data serialization.

My database server and PGAdming are running on the same computer, so this can't be a network bandwith 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.

Is the transfer time from PostgreSQL to the application optimisable?

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.

1
source | link

Does transfer time from PostgreSQL to application is optimisable?

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 datas. I have 250000 positions for each vehicles, and 5 vehicles. So I have 1000000 rows in the table.

In pgadmin i launch 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 ANAYLZE, there is no result data serialization.

My database server and PGAdming are running on the same computer, so this can't be a network bandwith 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.