0

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
    Hi , this might be just pgAdmin - try the same thing with psql and output to a file (using \o , to avoid screen time). 14 seconds is way tool long... btw, is this windows or linux? – cohenjo Oct 19 '16 at 14:04
  • It's a linux system. So you are right ! If I run the same query with psql in a terminal, request is processed in 800 ms ! – Adrien BARRAL Oct 19 '16 at 14:11
  • pgAdmin is known to be very slow when displaying large amount of data – a_horse_with_no_name Oct 19 '16 at 14:15
1

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).

  • Well, sometimes (not very often though) it might make sense to display that many rows. And other SQL clients can handle that a lot better – a_horse_with_no_name Oct 19 '16 at 14:18
  • I use pgAdmin just for testing... In my real application I have a sping JPA repository which will post the query, and process result. My initial problem is that the Spring JPA query is very long. So I used PGadmin to see if the problem is in my Spring JPA Configuration or in the database. Now, I know that database access is very quick (thansk to @a_horse_with_no_name). So I will investigate why my JPA query is so long, but on another stack exchange. Thanks a lot. – Adrien BARRAL Oct 19 '16 at 14:21
  • 3
    @AdrienBARRAL: I wouldn't be surprised if your obfuscation layer (aka ORM, JPA) also adds a substantial amount of overhead to the processing of the result. – a_horse_with_no_name Oct 19 '16 at 14:26
  • @AdrienBARRAL I thought you was interested in the content of the table returned by the query insted of simple query time execution :) – pietrop Oct 19 '16 at 14:31
  • Ok, JPA will add an overhead of processing, but I hop that if I can fetch my data in 800ms with psql, I hope that I will be able to fetch them in less than 10 seconds with JPA ! As you can see i am quite a noob in database, so I think I did something very bad with my JPA request... – Adrien BARRAL Oct 19 '16 at 14:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.