title: Two identical databases gives different execution plans
body: <p>We have created a demo PostgressSQL database at DO to test performance, tune it up with indexes and etc. After we achieved good performance results we decided to roll up pre-staging identical database on new Postgress database server with the same 16gb of ram and 6cpu on SSD, created a database using create a script and imported data. We were not using PDump. </p>
  
  <p>To our surprise, the query which we are suing for both databases runs 10 times slower on the pre-staging database than on demo.</p>
  
  <pre><code>EXPLAIN (buffers, analyse, timing )
  SELECT
      DATE("DateStamp"), "Result", Count(*), Sum("ConversionCost")
  FROM "Log"
  WHERE "UserId" = 7841
    AND "DateStamp" &gt; '2019-01-02'
    AND "DateStamp" &lt; '2020-02-26'
  GROUP BY 1,2
  </code></pre>
  
  <p>The execution plan for demo database</p>
  
  <pre><code>Finalize GroupAggregate  (cost=252123.56..660750.27 rows=3206490 width=21) (actual time=546.500..811.119 rows=714 loops=1)
  "  Group Key: (date(""DateStamp"")), ""Result"""
  "  Buffers: shared hit=5544, temp read=3903 written=3922"
    -&gt;  Gather Merge  (cost=252123.56..593948.38 rows=2672076 width=21) (actual time=545.942..810.651 rows=1811 loops=1)
          Workers Planned: 2
          Workers Launched: 2
  "        Buffers: shared hit=16004, temp read=11035 written=11090"
          -&gt;  Partial GroupAggregate  (cost=251123.54..284524.49 rows=1336038 width=21) (actual time=539.140..793.857 rows=604 loops=3)
  "              Group Key: (date(""DateStamp"")), ""Result"""
  "              Buffers: shared hit=16004, temp read=11035 written=11090"
                -&gt;  Sort  (cost=251123.54..254463.63 rows=1336038 width=9) (actual time=539.092..657.869 rows=1064916 loops=3)
  "                    Sort Key: (date(""DateStamp"")), ""Result"""
                      Sort Method: external merge  Disk: 23992kB
                      Worker 0:  Sort Method: external merge  Disk: 22808kB
                      Worker 1:  Sort Method: external merge  Disk: 22168kB
  "                    Buffers: shared hit=16004, temp read=11035 written=11090"
                      -&gt;  Parallel Index Only Scan using "IX_Log_UserId-all" on "Log"  (cost=0.56..72778.81 rows=1336038 width=9) (actual time=0.051..250.464 rows=1064916 loops=3)
                            Index Cond: (("UserId" = 7841) AND ("DateStamp" &gt; '2019-01-02 00:00:00'::timestamp without time zone) AND ("DateStamp" &lt; '2020-02-26 00:00:00'::timestamp without time zone))
                            Heap Fetches: 449
                            Buffers: shared hit=15974
  Planning Time: 0.144 ms
  Execution Time: 815.399 ms
  </code></pre>
  
  <p><a href="https://explain.depesz.com/s/IBYt" rel="nofollow noreferrer">https://explain.depesz.com/s/IBYt</a></p>
  
  <p>The execution plan for staging database</p>
  
  <pre><code>GroupAggregate  (cost=1682917.01..1760593.98 rows=3107079 width=21) (actual time=7180.989..7858.667 rows=714 loops=1)
  "  Group Key: (date(""DateStamp"")), ""Result"""
  "  Buffers: shared hit=21820 read=860249, temp read=8610 written=8627"
    -&gt;  Sort  (cost=1682917.01..1690684.71 rows=3107079 width=9) (actual time=7180.970..7506.138 rows=3194749 loops=1)
  "        Sort Key: (date(""DateStamp"")), ""Result"""
          Sort Method: external merge  Disk: 68880kB
  "        Buffers: shared hit=21820 read=860249, temp read=8610 written=8627"
          -&gt;  Index Scan using "IX_Log_UserId" on "Log"  (cost=0.56..1317519.17 rows=3107079 width=9) (actual time=0.077..6423.617 rows=3194749 loops=1)
                Index Cond: (("UserId" = 7841) AND ("DateStamp" &gt; '2019-01-02 00:00:00'::timestamp without time zone) AND ("DateStamp" &lt; '2020-02-26 00:00:00'::timestamp without time zone))
                Buffers: shared hit=21820 read=860249
  Planning Time: 0.169 ms
  Execution Time: 7868.180 ms
  </code></pre>
  
  <p><a href="https://explain.depesz.com/s/SDae" rel="nofollow noreferrer">https://explain.depesz.com/s/SDae</a></p>
  
  <p>Any idea where to look? The same resources, the same database with identical indexes, the same SQL query but different performance?!</p>