PostgreSQLquery speed is variable

Question

Context

I have a table that keeps netflow data (all packets intercepted by the router). This table features approximately 5.9 million rows at the moment.

Problem

I am trying a simple query to count the number of packets received by day, which should not take long.

The first time I run it, the query takes 88 seconds, then after a second run, 33 seconds, then 5 seconds for all subsequent runs.

The main problem is not the speed of the query, but rather that after executing the same query 3 times, the speed is nearly 20 times faster.
I understand the concept of query cache, however the performance of the original query run makes no sense to me.

Tests

The column that I am using to join (datetime) is of type timestamptz, and is indexed:

CREATE INDEX date ON netflows USING btree (datetime);

Looking at the EXPLAIN statements. The difference in execution is in the Nested Loop.

I have already VACUUM ANALYZE the table, with the exact same results.

Current environment

• Linux Ubuntu 12.04 VM running on VMware ESX 4.1
• PostgreSQL 9.1
• VM has 2 GB RAM, 2 cores.
• database server is entirely dedicated to this and is doing nothing else
• inserts in the table every minute (100 rows per minute)
• very low disk, ram or cpu activity

Query

with date_list as (
    select
        series as start_date,
        series + '23:59:59' as end_date
    from
        generate_series(
            (select min(datetime) from netflows)::date, 
            (select max(datetime) from netflows)::date, 
            '1 day') as series
)
select
    start_date,
    end_date,
    count(*)
from
    netflows
    inner join date_list on (datetime between start_date and end_date)
group by
    start_date,
    end_date;

Explain of first run (88 seconds)

Sort  (cost=27007355.59..27007356.09 rows=200 width=8) (actual time=89647.054..89647.055 rows=18 loops=1) 
  Sort Key: date_list.start_date 
  Sort Method: quicksort  Memory: 25kB 
  CTE date_list 
    ->  Function Scan on generate_series series  (cost=0.13..12.63 rows=1000 width=8) (actual time=92.567..92.667 rows=19 loops=1) 
          InitPlan 2 (returns $1) 
            ->  Result  (cost=0.05..0.06 rows=1 width=0) (actual time=71.270..71.270 rows=1 loops=1) 
                  InitPlan 1 (returns $0) 
                    ->  Limit  (cost=0.00..0.05 rows=1 width=8) (actual time=71.259..71.261 rows=1 loops=1) 
                          ->  Index Scan using date on netflows  (cost=0.00..303662.15 rows=5945591 width=8) (actual time=71.252..71.252 rows=1 loops=1) 
                                Index Cond: (datetime IS NOT NULL) 
          InitPlan 4 (returns $3) 
            ->  Result  (cost=0.05..0.06 rows=1 width=0) (actual time=11.786..11.787 rows=1 loops=1) 
                  InitPlan 3 (returns $2) 
                    ->  Limit  (cost=0.00..0.05 rows=1 width=8) (actual time=11.778..11.779 rows=1 loops=1) 
                          ->  Index Scan Backward using date on netflows  (cost=0.00..303662.15 rows=5945591 width=8) (actual time=11.776..11.776 rows=1 loops=1) 
                                Index Cond: (datetime IS NOT NULL) 
  ->  HashAggregate  (cost=27007333.31..27007335.31 rows=200 width=8) (actual time=89639.167..89639.179 rows=18 loops=1) 
        ->  Nested Loop  (cost=0.00..23704227.20 rows=660621222 width=8) (actual time=92.667..88059.576 rows=5945457 loops=1) 
              ->  CTE Scan on date_list  (cost=0.00..20.00 rows=1000 width=16) (actual time=92.578..92.785 rows=19 loops=1) 
              ->  Index Scan using date on netflows  (cost=0.00..13794.89 rows=660621 width=8) (actual time=2.438..4571.884 rows=312919 loops=19) 
                    Index Cond: ((datetime >= date_list.start_date) AND (datetime <= date_list.end_date)) 
Total runtime: 89668.047 ms 

EXPLAIN of third run (5 seconds)

Sort  (cost=27011357.45..27011357.95 rows=200 width=8) (actual time=5645.031..5645.032 rows=18 loops=1) 
  Sort Key: date_list.start_date 
  Sort Method: quicksort  Memory: 25kB 
  CTE date_list 
    ->  Function Scan on generate_series series  (cost=0.13..12.63 rows=1000 width=8) (actual time=0.108..0.204 rows=19 loops=1) 
          InitPlan 2 (returns $1) 
            ->  Result  (cost=0.05..0.06 rows=1 width=0) (actual time=0.050..0.050 rows=1 loops=1) 
                  InitPlan 1 (returns $0) 
                    ->  Limit  (cost=0.00..0.05 rows=1 width=8) (actual time=0.046..0.046 rows=1 loops=1) 
                          ->  Index Scan using date on netflows  (cost=0.00..303705.14 rows=5946469 width=8) (actual time=0.046..0.046 rows=1 loops=1) 
                                Index Cond: (datetime IS NOT NULL) 
          InitPlan 4 (returns $3) 
            ->  Result  (cost=0.05..0.06 rows=1 width=0) (actual time=0.026..0.026 rows=1 loops=1) 
                  InitPlan 3 (returns $2) 
                    ->  Limit  (cost=0.00..0.05 rows=1 width=8) (actual time=0.026..0.026 rows=1 loops=1) 
                          ->  Index Scan Backward using date on netflows  (cost=0.00..303705.14 rows=5946469 width=8) (actual time=0.026..0.026 rows=1 loops=1) 
                                Index Cond: (datetime IS NOT NULL) 
  ->  HashAggregate  (cost=27011335.17..27011337.17 rows=200 width=8) (actual time=5645.005..5645.009 rows=18 loops=1) 
        ->  Nested Loop  (cost=0.00..23707741.28 rows=660718778 width=8) (actual time=0.134..4176.406 rows=5946329 loops=1) 
              ->  CTE Scan on date_list  (cost=0.00..20.00 rows=1000 width=16) (actual time=0.110..0.343 rows=19 loops=1) 
              ->  Index Scan using date on netflows  (cost=0.00..13796.94 rows=660719 width=8) (actual time=0.026..164.117 rows=312965 loops=19) 
                    Index Cond: ((datetime >= date_list.start_date) AND (datetime <= date_list.end_date)) 
Total runtime: 5645.189 ms

Answer 1

If you are doing an INNER JOIN I don't think you need the CTE at all. You can define

select
    datetime::date,
    count(*)
from netflows
group by datetime::date /* or GROUP BY 1 as Postgres extension */

I don't see why you need the dates table unless you want a LEFT JOIN to get zeroes where appropriate. This will mean one pass through the data.

BTW, I discourage you from using keywords like date and datetime for entities and columns; even when it's legal, it's not worth it.