1

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.

3
  • 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
    Commented Oct 19, 2016 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 ! Commented Oct 19, 2016 at 14:11
  • 1
    pgAdmin is known to be very slow when displaying large amount of data
    – user1822
    Commented Oct 19, 2016 at 14:15

1 Answer 1

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

7
  • 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
    – user1822
    Commented Oct 19, 2016 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. Commented Oct 19, 2016 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.
    – user1822
    Commented Oct 19, 2016 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
    Commented Oct 19, 2016 at 14:31
  • 1
    @AdrienBARRAL ORMs are not designed to do bulk processing. They are designed around the idea of working with single Objects (=rows) and thus usually deliver a horrible performance for anything that touches a lot of rows.
    – user1822
    Commented Oct 19, 2016 at 14:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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