Re: FreeBSD UFS & fsync
On Fri, Mar 12, 2021 at 10:34 AM Thomas Munro wrote: > It looks like your system is performing very badly for some other > reason, so that synchronous I/O waits are only a small proportion of > the time, and thus fsync=off doesn't speed things up very much. I'd > look into profiling the system to try to figure out what it's doing... > maybe it's suffering from super slow hypercalls for gettimeofday(), or > something like that? Let me get this straight to see if I understand it correctly: pg_test_fsync reports 278000 tps in non sync-ed mode, and that is what I should expect (nearly) from turning off fsyc. However, something else is eating my resources, so I'm not getting the correct results. Now, what do you mean by profiling the system? Since I'm on FreeBSD I could use dtrace to see if there's any clue where the time is spent, even if I'm not so expert in dtrace. Please also note that pg_test_timing seems fine to me (I've tried several times with pretty much the same results): % pg_test_timing Testing timing overhead for 3 seconds. Per loop time including overhead: 37.68 ns Histogram of timing durations: < us % of total count 1 96.46399 76796834 2 3.524172805657 4 0.00400 3183 8 0.00320 2546 16 0.00235 1871 32 0.00124988 64 0.00065517 128 0.00024189 256 0.7 58 512 0.3 26 1024 0.2 18 2048 0.2 19 4096 0.1 9 8192 0.0 1 So apparently gettimeofday should not be the problem right here. Luca
wide table, many many partitions, poor query performance
All; We have a client that is running PostgreSQL 12, they have a table with 212 columns and 723 partitions It seems the planning time is consumed by generating 723 sub plans I suspect it's due to the fact that they are using hash based partitioning, example: CREATE TABLE rental_transaction_hash_p723 PARTITION OF rental_transaction FOR VALUES WITH (MODULUS 723, REMAINDER 723); Based on a strategy like this, queries will ALWAYS scan all partitions unless a hash value is specified as part of the query, correct? I suspect this is the issue... looking for confirmation, or feedback if i'm off base Thanks in advance
Re: wide table, many many partitions, poor query performance
On Mon, Mar 15, 2021 at 10:53:06AM -0600, S Bob wrote: > We have a client that is running PostgreSQL 12, they have a table with 212 > columns and 723 partitions > > It seems the planning time is consumed by generating 723 sub plans Is plannning time the issue ? Please show diagnostic output. You can start from here: https://wiki.postgresql.org/wiki/Slow_Query_Questions > I suspect it's due to the fact that they are using hash based partitioning, > example: > > CREATE TABLE rental_transaction_hash_p723 PARTITION OF rental_transaction > FOR VALUES WITH (MODULUS 723, REMAINDER 723); > > Based on a strategy like this, queries will ALWAYS scan all partitions > unless a hash value is specified as part of the query, correct? I suspect > this is the issue... looking for confirmation, or feedback if i'm off base You didn't say anything about the query, so: yes, maybe. The partition strategy and key need to be selected to optimize the intended queries. Hash partitioning is frequently a mistake. See also: https://www.postgresql.org/message-id/[email protected] https://www.postgresql.org/message-id/[email protected] -- Justin
Re: wide table, many many partitions, poor query performance
On Mon, 2021-03-15 at 10:53 -0600, S Bob wrote: > We have a client that is running PostgreSQL 12, they have a table with > 212 columns and 723 partitions > > It seems the planning time is consumed by generating 723 sub plans > > I suspect it's due to the fact that they are using hash based > partitioning, example: > > CREATE TABLE rental_transaction_hash_p723 PARTITION OF > rental_transaction FOR VALUES WITH (MODULUS 723, REMAINDER 723); > > Based on a strategy like this, queries will ALWAYS scan all partitions > unless a hash value is specified as part of the query, correct? I > suspect this is the issue... looking for confirmation, or feedback if > i'm off base That is correct. The only use I can see in hash partitioning is to put the partitions on different storage devices in order to spread I/O - kind of striping on the database level. Unless you can benefit from that, your queries will become slower. Yours, Laurenz Albe
