0

I have a table that keeps measurements of latencies between nodes running MPI tasks in a large cluster. The table looks like this:

CREATE TABLE latency(
from_rank int,
to_rank int,
from_host varchar(20),
to_host varchar(20),
from_cpu varchar(20),
to_cpu varchar(20),
latency float8);

CREATE INDEX ON latency(from_host, to_host);

Now after a large experiment I collected over 500 million rows of data. I find querying these data painfully slow, below is an example of a SELECT COUNT(*)

psql (9.4devel)
Type "help" for help.

routing=# \timing 
Timing is on.
routing=# SELECT COUNT(*) FROM latency;
   count   
-----------
 522190848
(1 row)

Time: 759462.969 ms
routing=# SELECT COUNT(*) FROM latency;
   count   
-----------
 522190848
(1 row)

Time: 96775.036 ms
routing=# SELECT COUNT(*) FROM latency;
   count   
-----------
 522190848
(1 row)

Time: 97708.132 ms
routing=#

I am running both the PgSQL server and client on the same machine, which has 4 Xeon E7-4870s (40 cores/80 threads in total) and 1 TB of RAM. The effect of Linux file caching is obvious: the first query took well over 12mins while the subsequent ones took about 1.5min.

Is there anything I can do to make the query run faster, since 1.5min isn't exactly responsive.

Thanks.

  • 3
    We can't help you diagnose this other query without seeing it. – Andy Lester Jul 23 '13 at 21:25
  • 1
    How about posting some details about your server, along with the real queries that are not performing. Start with the output of the following query, followed by EXPLAIN (ANALYZE,BUFFERS) of your slow query. SELECT name, current_setting(name), source FROM pg_settings WHERE source NOT IN ('default', 'override') UNION ALL SELECT 'version' as name, version(), null; Also, did you run ANALYZE before running your slow query(ies)? – bma Jul 23 '13 at 22:33
  • 5
    Being in charge of a 500mm row table on your kind of hardware and not knowing about EXPLAIN are two wildly conflicting pieces of information. You may need someone with a little experience to configure your system properly. Or you need to start reading. – Erwin Brandstetter Jul 23 '13 at 22:56
  • 1
    Here is a good guide to posting performance problems (generally to the postgresql mailing lists, but it applies to S.O. too): wiki.postgresql.org/wiki/SlowQueryQuestions – bma Jul 23 '13 at 23:01
  • 1
    Please see the guidance here: stackoverflow.com/tags/postgresql-performance/info for good info to collect for diagnostics. – Craig Ringer Jul 24 '13 at 2:25
3

For postgres, unlike other RDBMS, SELECT COUNT(*) ... from an unindexed table means a full scan of the table. So the first run is purely IO bound. If you want this query - consider adding some indexes to the table. (Without knowing the query I can not tell, what indexes you may need).

Another option - configure postgres to better utilize the RAM you have.

  • 3
    This is true even on a table with a suitable index until 9.2 (with index-only scans). – Craig Ringer Jul 24 '13 at 2:26
  • Every DBMS that implements proper transaction handling will need to scan the whole table if no index is available. – a_horse_with_no_name Jul 24 '13 at 21:15

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.