I'm working with postgres for the first time. I have a lot of experience with small / medium size data analysis (i.e. things that fit in ram and can be analyzed in R, Stata, Matlab, etc.), but am now working with big data (300-750gb) for the first time.

As a result, I have no concept of how long things should be taking. I think my database is performing very poorly, but having never worked on these scales I don't really know.

So here's my question: even basic queries are taking me at least 8 hours on a 237gb table. Vacuum takes ~6 hours. And a query pulling out distinct pairs of values:

SELECT DISTINCT a, group_a, sum(quantity) FROM cdr GROUP BY (a, group_a) HAVING type = 'DATA' AND group_a IS NOT NULL;

ran for 8 hours before I aborted.

An attempt to build 4 hash indices over main columns ran for 24 hours then crashed.

Hardware: 3 cores, 12 gb ram Windows 8 server VM. (I know, but I don't have control over my hardware. Long story).

So basically: within an order of magnitude, how long should I expect basic queries to take in postgres for tables of this size?

And if this seems way off, how do I get more precise benchmarks? I'm running pgbench now, but can't find resources on how to interpret the results. This listserv exchange suggests there aren't any repositories of results...

  • 1.5 billion rows.
  • Settings are default, looks like work_mem = 1mb, maintanence_work_mem = 16mb.
  • IO System: it's a VM so the disk is just listed as a "VMware virtual disk SCSI disk device", but Performance Monitor says avg Disc sec/transfer is 0.010sec and average disk queue length is 2.16.
  • At first glance - week hardware. In any case there is always a very big potential in optimization.
    – Str.
    Apr 13, 2014 at 20:12
  • Please don't cross-post... this question is on it's way over there shortly, but now there's no point. If you're doing this then you're not going to be able to put it all on RAM and you're only going to use one core so the biggest potential problem is going to be disk I/O. You need good disks...
    – Ben
    Apr 13, 2014 at 20:41
  • wiki.postgresql.org/wiki/Slow_Query_Questions
    – user1822
    Apr 13, 2014 at 21:22
  • You haven't described your IO subsystem, which is critical. What configuration settings are you using, particularly work_mem and maintenance_work_mem?
    – jjanes
    Apr 13, 2014 at 22:35
  • Re: settings are default, looks like work_mem = 1mb, maintanence_work_mem = 16mb. (I'm gonna guess that's a problem?). Re: IO: it's a VM so the disk is just listed as a "VMware virtual disk SCSI disk device", but Performance Monitor says avg Disc sec/transfer is 0.010sec and average disk queue length is 2.16. (guessing at what's useful based on this. Thanks everyone for bearing with such a noob (I'm a social scientist if that helps explain ignorance, but i'm learning)! Also, will avoid cross posting in the future.
    – nick_eu
    Apr 14, 2014 at 1:30

2 Answers 2


You have to first set expectations - a screen that does such and such activities should complete each action in 1 second and all actions in 5 seconds and so on. For example, a search screen should retrieve results in 3 seconds, the booking actions (ticket booking) should be completed in 30 seconds etc.

Then work towards meeting those targets. That is the "normal" performance you want. Now go about meeting those targets. The database may be your bottleneck, it may not be. To identify issues at the database side, try using a tool like pgbadger. That will tell you which queries are taking time.

By the way, 8 hours for a query is probably not acceptable under any circumstance. Try the tool pgtune and see if there is scope for optimizing parameters.


If you are grouping you don't need a distinct and move the conditions from the having to the where

select a, group_a, sum(quantity)
from cdr
where type = 'DATA' and group_a is not null
group by a, group_a;
  • Thanks! But specifics aside, I'm trying to get a GENERAL sense of whether my system is performing normally...
    – nick_eu
    Apr 14, 2014 at 3:02
  • 1
    @nick_eu: The "specifics" can make the resulting performance differ by orders of magnitude. With billions of rows, the type should also probably not be a text string to keep the size of the table small ... Apr 14, 2014 at 16:07

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.