Slow queries on billions-rows-table // index used Since I'm a young developer and not really skilled in using databases (PostgreSQL 9.3) I ran into some problems with a project, where I really need help with.

My project is about collecting data from devices (up to 1000 or more devices), where every device is sending one data block every second, which makes about 3 million rows per hour.

Currently I've got one big table where I store the incoming data of every device:

CREATE TABLE data_block(
    id bigserial
    timestamp timestamp
    mac bigint
)


Because there are several types of data a data block can (or can not) include, there are other tables which reference the data_block table.

CREATE TABLE dataA(
    data_block_id bigserial
    data

    CONSTRAINT fkey FOREIGN KEY (data_block_id) REFERENCES data_block(id);
);
CREATE TABLE dataB(...);
CREATE TABLE dataC(...);
CREATE INDEX index_dataA_block_id ON dataA (data_block_id DESC);
...


It is possible that in one data_block there is 3x dataA, 1x dataB, but no dataC.

The data will be kept for some weeks, so I'm going to have ~5 billion rows in this table. At the moment, I have ~600 million rows in the table and my queries take a really long time. So I decided to make an index over timestamp and mac, because my select statements always query over time and often also over time+mac.

CREATE INDEX index_ts_mac ON data_block (timestamp DESC, mac);


...but my queries still take ages. For example, I queried data for one day and one mac:

SELECT * FROM data_block 
WHERE timestamp&gt;'2014-09-15' 
AND timestamp&lt;'2014-09-17' 
AND mac=123456789


Index Scan using index_ts_mac on data_block  (cost=0.57..957307.24 rows=315409 width=32) (actual time=39.849..334534.972 rows=285857 loops=1)
  Index Cond: ((timestamp &gt; '2014-09-14 00:00:00'::timestamp without time zone) AND (timestamp &lt; '2014-09-16 00:00:00'::timestamp without time zone) AND (mac = 123456789))
Total runtime: 334642.078 ms


I did a full vacuum before query run. Is there an elegant way to solve such a problem with big tables to do an query &lt;10sec?

I read about partitioning, but this won't work with my dataA, dataB, dataC references to data_block_id right? If it would work somehow, should I make partitions over time or over mac?

I changed my index to the other direction. First MAC, then timestamp, and it gains a lot of performance.

CREATE INDEX index_mac_ts ON data_block (mac, timestamp DESC);


But still, queries take >30sec. Especially when I do a LEFT JOIN with my data tables. Here is an EXPLAIN ANALYZE of the query with the new index:

EXPLAIN ANALYZE SELECT * FROM data_block WHERE mac = 123456789 AND timestamp &lt; '2014-10-05 00:00:00' AND timestamp &gt; '2014-10-04 00:00:00'


Bitmap Heap Scan on data_block  (cost=1514.57..89137.07 rows=58667 width=28) (actual time=2420.842..32353.678 rows=51342 loops=1)
  Recheck Cond: ((mac = 123456789) AND (timestamp &lt; '2014-10-05 00:00:00'::timestamp without time zone) AND (timestamp &gt; '2014-10-04 00:00:00'::timestamp without time zone))
  -&gt;  Bitmap Index Scan on index_mac_ts  (cost=0.00..1499.90 rows=58667 width=0) (actual time=2399.291..2399.291 rows=51342 loops=1)
        Index Cond: ((mac = 123456789) AND (timestamp &lt; '2014-10-05 00:00:00'::timestamp without time zone) AND (timestamp &gt; '2014-10-04 00:00:00'::timestamp without time zone))
Total runtime: 32360.620 ms 


Unfortunately my hardware is strictly limited. I'm using an Intel i3-2100 @3.10Ghz, 4GB RAM. My current settings are as following:

default_statistics_target = 100
maintenance_work_mem = 512MB
constraint_exclusion = on
checkpoint_completion_target = 0.9
effective_cache_size = 4GB
work_mem = 512MB
wal_buffers = 16MB
checkpoint_segments = 32
shared_buffers = 2GB
max_connections = 20
random_page_cost = 2
This may reflect my MS SQL bias, but I'd try clustering the table by timestamp.  If you're frequently pulling data for a specific time span, this will help because the data will be physically stored contiguously.  The system can seek to the start point, scan to the end of the range, and be done.  If you're querying for a specific hour, that's just 3,600,000 records.

If your query (which is...?) is for a specific machine, Postgres will then need to filter out 99.9% of those 3.6 M records.  If this one-in-a-thousand filter is more selective than a typical date range fitler, you should use the more selective mac field as the first component of your index.  It may still be worth clustering.

If that still doesn't do it, I'd partition by the same field you're indexing, either timestamp or mac.

You didn't give the data types.  Are they appropriate to the data?  Storing dates as text will needlessly bloat your table, for example.