Results 1 to 2 of 2

Thread: Why are my queries slower on Postgres v.13 than they were on v.9.5?

  1. #1
    Join Date
    Nov 2020
    Location
    Utah
    Posts
    6

    Question Why are my queries slower on Postgres v.13 than they were on v.9.5?

    I am building a new kind of database engine based on an object store that I invented. I like to benchmark it against popular databases like Postgres, MS Sql Server, and MySQL. It has been a few years since I worked closely with Postgres so I had been previously using version 9.5 and PgAdmin 3. Since I upgraded my computer, I was re-running my old tests to see how much faster things would run on my new AMD Ryzen 5950x system. I decided that I should upgrade Postgres as well so I downloaded version 13 along with PgAdmin 4. I had heard that Postgres made a bunch of improvements in threading a single query so, like my engine, it would take advantage of all those nice cores in my CPU.

    When running my tests using a public data set (21 years of Chicago Crime data available for download on their open data portal) I noticed that many of the queries actually ran slower than they did on version 9.5. This surprised me since a few other things ran much faster. Importing the CSV file into the table was much faster. Doing a "Select *" query from the 7.4 million row table also showed data in just a few seconds since it seems to show the first few pages right away while it continues to run the query in the background (the old PgAdmin 3 took over 20 minutes to show you anything).

    But the queries themselves ran slower. Maybe the table isn't big enough to kick in the parallel execution feature (I did see 2 workers dispatched on a few queries, but no where near the 32 threads my CPU supports). Is there something the default setup doesn't enable so that queries would run faster? I tried it with and without indexes against the columns I was querying against but in most cases, it still took longer than it did on version 9.5.

    I posted this video on my YouTube channel that shows a few of the queries I was running during the first couple minutes. Any ideas on why they are slower?
    Last edited by AndyL; 11-18-2021 at 07:47 PM.

  2. #2
    Join Date
    Nov 2020
    Location
    Utah
    Posts
    6
    Update: I looked at the configuration file and discovered when I upgraded to version 13 it did not keep my old one but set everything to default values. (I can't believe they still have the default for shared_buffers at 128MB!) So I bumped it up to 2GB and didn't notice a difference. When I set it to 8GB then the queries were noticeably faster. They still are not faster than I had running with version 9.5, but now at least they are not significantly slower. I had expected a big boost in performance by upgrading but I am just not seeing it. Is that other's experience as well?
    Last edited by AndyL; 11-12-2021 at 11:18 AM.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •