2018-01-15 9:13 GMT-08:00 Vick Khera <vivek@xxxxxxxxx>:
The problem is that this plan is saved in the database, I have a Java application that executes 6 times a query and saves the result of Explain Analyze to a table in my Database. Upon regaining the execution plan, it loses the line breaks, unfortunately. I'm checking how to change the java application, I sent a question in the java forum because I do not know how to solve this other problem yet: My question in Java forum: https://stackoverflow.com/questions/48267819/save-line-break-in-database-in-text-field
Try random page cost 1.1. Way back when I started using SSD we had a discussion here and came to the conclusion that it should be ever so slightly higher than sequential page cost.
Very good tip, I'm running the query with random_page_cost = 1.1, but notice that there are no secondary indexes on my database.
The test you were doing is to first run the query without index, and then create an index to check the performance improvement.
But what I reported that having problem, is that the execution of the query without index in a SAS HDD is being much faster, than the query (without index) in the SSD, and I found this very strange, see below:
- Query execution Time on SSD - Average: 26min 54 seconds
- Query execution Time on HDD SAS 15K - Average: 12 minutes 48 seconds
The test you were doing is to first run the query without index, and then create an index to check the performance improvement.
But what I reported that having problem, is that the execution of the query without index in a SAS HDD is being much faster, than the query (without index) in the SSD, and I found this very strange, see below:
- Query execution Time on SSD - Average: 26min 54 seconds
- Query execution Time on HDD SAS 15K - Average: 12 minutes 48 seconds
It is very hard to read your query plans (maybe gmail is wrapping them funny or you need to use a fixed font on them or share them from https://explain.depesz.com), but they do look substantially different. My guess is that with the random page cost = sequential page cost you are tricking Pg into using more sequential scans than index searches.
The problem is that this plan is saved in the database, I have a Java application that executes 6 times a query and saves the result of Explain Analyze to a table in my Database. Upon regaining the execution plan, it loses the line breaks, unfortunately. I'm checking how to change the java application, I sent a question in the java forum because I do not know how to solve this other problem yet: My question in Java forum: https://stackoverflow.com/questions/48267819/save-line-break-in-database-in-text-field
