0

My SysAdmin colleague told me that the Postgres'hosts use NVME disk.

  • How can I check that with Linux command?
  • Why does the planner/optimizer seem to get it wrong when I set random_page_cost =1.1

To bench it, I created table t_1(id int):

CREATE TABLE t_1 (id int);

In the id column, I inserted random numbers between 1 and 10,000,000. I inserted 10 millions rows.

Then:

CREATE INDEX ON t_1(id);
SET random_page_cost =1.1;

The query, using a Seq Scan, looks like this:

EXPLAIN (ANALYZE,BUFFERS) SELECT * from t_1 where id > 1600000;

And the result like this:

 Seq Scan on t_1  (cost=0.00..169248.00 rows=8361406 width=4) (actual time=0.010..917.250 rows=8399172 loops=1)
   Filter: (id > 1600000)
   Rows Removed by Filter: 1600828
   Buffers: shared hit=44248
Planning Time: 0.079 ms
Execution Time: 1301.160 ms
(6 lignes)

The query, using a Seq Scan, looks like that:

EXPLAIN (ANALYZE,BUFFERS) SELECT * from t_1 where id > 1700000;

And the result like that:

 Index Only Scan using t_1_id_idx on t_1  (cost=0.43..166380.65 rows=8258658 width=4) (actual time=0.027..1309.272 rows=8300257 loops=1)
  Index Cond: (id > 1700000)
  Heap Fetches: 0
  Buffers: shared hit=3176619
Planning Time: 0.070 ms
Execution Time: 1698.532 ms
(6 lignes)

On the internet, I read that it is recommended to set the random_page_cost ~= seq_page_cost because random accesses are as fast as sequential accesses with NVME disks. Using an index requires reading random pages from disk; whereas reading the full table gets to read pages sequentially. By decreasing the random_page_cost parameter, I notice that the planner favors the use of Indexes as expected, but to my surprise the execution time deteriorates.

  • My manager ask me to bench the random_page_cost parameter. How can I bench random_page_cost ?
Eric Renault
  • 15
  • 3
  • Both queries get 100% of their results from cache, not from disk. See the shared hit rates. Forcing the database to use the index is way more CPU intensive than a simple sequential read. And one single query to measure overall performance, doesn't proof a lot. And random_page_cost is just one of many settings, also doesn't proof a lot when you optimizing performance. How does the big picture looks like? – Frank Heikens Aug 01 '22 at 17:12
  • Yes, both queries get 100% of their results from cache, I agree. If using an index is more intensive, the planner should be aware of it. The theorical cost should not be lower. Look at the cost. The cost decreases while the actual time increases. It is a sandbox server, I don't have a big picture. – Eric Renault Aug 02 '22 at 07:26
  • The planner is aware of this, see for example cpu_tuple_cost and cpu_index_tuple_cost. But these are just two settings, out of a 40 different settings to create a query plan. And don't forget, a plan is just a plan. A good plan is something else. And your current setting for random_page_cost, in combination with the all the other current settings, is just not the right one for this single query. You're telling the database that it should (almost?) always use an index scan and this turns out to be the slower plan. – Frank Heikens Aug 02 '22 at 14:11
  • You're telling the database that it should (almost?) always use an index scan and this turns out to be the slower plan. => No, I am looking for the best value for random_page_cost that fit to my environnement (NVMe disk). I choosed to run a query that has a cost quite similar if using the index or Seq Scan. When I lower random_page_cost, the cost for Index Scan dicreases however the actual time increases. According to documentation on internet, I should decrease ramdom_page_cost. According the actual time of my query, I should leave the default value. What do you recommend ? – Eric Renault Aug 02 '22 at 15:50
  • Start testing your entire application, to find the slow queries using a sequential read where you would expect an index read. Then you can tweak random_page_cost, but you also have to check many other settings, like cpu_tuple_cost and cpu_index_tuple_cost. And maybe even change/optimize the statistics for your tables. random_page_cost on its own, is unlikely to be the silver bullet for all performance issues. – Frank Heikens Aug 02 '22 at 16:08
  • When using a fast drive like an NVME drive, I think the choice between sequential read and index read, is more related to the amount of work the CPU has to do and the interaction with RAM. – Frank Heikens Aug 02 '22 at 16:37
  • I cannot simulate application activity on my sandbox server. I don't have performance problem. I just want to align the parameters (random_page_cost) with the hardware (MVNe disks). Then check the effect of the change. – Eric Renault Aug 03 '22 at 10:14

0 Answers0