Postgresql 12, 512 partition by hash. Slow select

From: Arya F <arya6000(at)gmail(dot)com>
To: pgsql-performance(at)lists(dot)postgresql(dot)org
Subject: Postgresql 12, 512 partition by hash. Slow select
Date: 2020-04-05 17:48:03
Message-ID: CAFoK1axr_T6nB8ZAq8g2QBcqv_pE=dsZsxyjatz8Q67k1VKAnw@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

I have a table with 120 million rows of data spread among 512
partitioned by hash table. The id column of the table is a uuid, which
is what is being used for the partition hash and it's also the PK for
the table.

The table has a text column, which also has a btree index on it. A
select query on an identical non-partitioned table takes 0.144
seconds, but on the partitioned table it takes 5.689 seconds.

Am I missing something in my setup? Or is this expected? I do know
having more than 100 partitions in prior versions of PostgreSQL 12
would cause a major slow down, but from what I read PostgreSQL 12
addresses that now?

https://www.2ndquadrant.com/en/blog/postgresql-12-partitioning/

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Andreas Kretschmer 2020-04-05 18:41:03 Re: Postgresql 12, 512 partition by hash. Slow select
Previous Message Julien Rouhaud 2020-04-04 07:06:38 Re: BUG #16334: We recently upgraded PG version from 9.5 to 10.10 and system performance is not so good