2

I have 2 databases running on the same server. Both are development databases with the same structure, but different amounts of data. Running Postgres 9.2 on Linux (Centos/Redhat)

I'm running the following SQL on each database, but getting very different results in the performance.

Note: the write_history table structure is identical in each DB and have the same indexes/constraints.

Here is the SQL:

 explain analyze SELECT * FROM write_history WHERE fk_device_rw_id =
 'd2c969b8-2609-11e3-80ca-0750cff1e96c'  AND fk_write_history_status_id
 = 5  ORDER BY update_time DESC LIMIT 1 ;

And the Explain Plans for each DB:

DB1 - PreProd

 Limit  (cost=57902.39..57902.39 rows=1 width=103) (actual
 time=0.056..0.056 rows=0 loops=1)'   ->  Sort 
 (cost=57902.39..57908.69 rows=2520 width=103) (actual
 time=0.053..0.053 rows=0 loops=1)'
         Sort Key: update_time'
         Sort Method: quicksort  Memory: 25kB'
         ->  Bitmap Heap Scan on write_history  (cost=554.04..57889.79 rows=2520 width=103) (actual time=0.033..0.033 rows=0 loops=1)'
               Recheck Cond: (fk_device_rw_id = 'd2c969b8-2609-11e3-80ca-0750cff1e96c'::uuid)'
               Filter: (fk_write_history_status_id = 5)'
               ->  Bitmap Index Scan on idx_write_history_fk_device_rw_id  (cost=0.00..553.41 rows=24034
 width=0) (actual time=0.028..0.028 rows=0 loops=1)'
                     Index Cond: (fk_device_rw_id = 'd2c969b8-2609-11e3-80ca-0750cff1e96c'::uuid)' Total runtime: 0.112 ms'

DB2 - QA

 Limit  (cost=50865.41..50865.41 rows=1 width=108) (actual
 time=545.521..545.523 rows=1 loops=1)'   ->  Sort 
 (cost=50865.41..50916.62 rows=20484 width=108) (actual
 time=545.518..545.518 rows=1 loops=1)'
         Sort Key: update_time'
         Sort Method: top-N heapsort  Memory: 25kB'
         ->  Bitmap Heap Scan on write_history  (cost=1431.31..50762.99 rows=20484 width=108) (actual time=21.931..524.034 rows=22034
 loops=1)'
               Recheck Cond: (fk_device_rw_id = 'd2cd81a6-2609-11e3-b574-47328bfa4c38'::uuid)'
               Rows Removed by Index Recheck: 1401986'
               Filter: (fk_write_history_status_id = 5)'
               Rows Removed by Filter: 40161'
               ->  Bitmap Index Scan on idx_write_history_fk_device_rw_id  (cost=0.00..1426.19 rows=62074
 width=0) (actual time=19.167..19.167 rows=62195 loops=1)'
                     Index Cond: (fk_device_rw_id = 'd2cd81a6-2609-11e3-b574-47328bfa4c38'::uuid)' Total runtime: 545.588 ms'

So a few questions:

  1. what is the difference between "Sort Method: quicksort Memory: 25kB" and "Sort Method: top-N heapsort Memory: 25kB" ?

  2. What could be the cause for the total runtime to be so different?

Table Row Count:

DB1 : write_history row count: 5,863,565

DB2 : write_history row count: 2,670,888

Please let me know if further information is required. Thanks for the help.

Improve this question
7
  • Are you sure you are running the same version of PostgreSQL on each? Commented Jul 14, 2014 at 13:26
  • do you have same memory configuration on both instances? Commented Jul 14, 2014 at 13:50
  • 2 databases running on the same server You mean same machine or same Postgresql server instance? Commented Jul 14, 2014 at 13:54
  • Both DB's are running on the same server/same instance yes. Commented Jul 14, 2014 at 14:33
  • Please show un-mangled query plans as printed by PostgreSQL. What's with the > quoting? It makes them really hard to read. Commented Jul 14, 2014 at 15:28

2 Answers 2

Reset to default
1

A top-N sort means that it is sorting in support of an ORDER BY ... LIMIT N, and that it will throw away any tuples once it can show that tuple cannot be in the top N. The decision to switch to a top-N sort is made dynamically during the sort process. Since that sort had zero input tuples, it never made the decision to switch to it. So the difference in the reported methods is an effect, not a cause; and is no importance to this case.

I think the key for you is the bitmap heap scans:

(actual time=0.033..0.033 rows=0 loops=1)

(actual time=21.931..524.034 rows=22034 loops=1)

The smaller database has a lot more rows which match your criteria, so has a lot more work to do.

Also, the amount of rechecking work that needs to be done:

Rows Removed by Index Recheck: 1401986

suggests you have work_mem set to an very small value of work_mem, so your bitmap is overflowing it.

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

Thanks very much for this suggestion. I increased work_mem from the default 1MB to 4MB. The query against the table in DB2 dropped down from 36 secs to 8 secs. Far more manageable now.
1

1) Two different sorting algorithms.

http://en.wikipedia.org/wiki/Quicksort

http://en.wikipedia.org/wiki/Heapsort

2) Volume of data being sorted will change the decision.

From memory of database theory; QuickSort performs very badly on large volumes of data due to the sequence it scans through the data. It's particularly bad if sorting requires caching to disk. Heap sort has a much better worst case than quick sort. The query planner knows this and will change the plan accordingly.

The number of rows being sorted is 8X larger. Be very careful to notice the number of rows expected to be selected as well as the number of rows in the table. The query planner uses histograms of the data to get a better picture of what will be selected not simply the number of rows in the table. In DB1 it's 2.5K, in DB2 it's 20K

You can check if these estimates are correct by checking if the resulting set of rows matches the count in the estimate. If not then consider performing an ANALYZE

Improve this answer

1 Comment

Thank you for your reply. Appreciate the information.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.