Re: Help understand why DELETE is so slow
Ping Yao writes: > Thank you for the comment. I think we do. What I found odd here is the > Query Plan (with analyze) clearly shows the actual execution to be very > quick, that's why I don't think it's an index problem. What do you think? I wouldn't rule it out on that basis. Foreign key enforcement is done by AFTER triggers, which are not part of the query plan. Having said that, all modern versions of PG show trigger runtime as a separate EXPLAIN output line, so slow FK checks aren't that hard to diagnose. I agree with the other comments that this is likely a Citus-specific issue, and that you came to the wrong place for expertise on that. regards, tom lane
Re: Help understand why DELETE is so slow
On Mon, Dec 4, 2023 at 3:43 PM Ping Yao wrote: > > Hi Merlin, > > Thank you for the comment. I think we do. What I found odd here is the > Query Plan (with analyze) clearly shows the actual execution to be very > quick, that's why I don't think it's an index problem. What do you think? > I'm not sure with citus. it isn't hard to verify; just check the referring tables and ensure any relating key is indexed. Perhaps the citus based plans don't surface that item -- hard to say. However, when you have complaints about slow deletes, this is always first thing to check. Second thing is slow triggers. merlin >
Re: Help understand why DELETE is so slow
Hi Merlin, Thank you for the comment. I think we do. What I found odd here is the Query Plan (with analyze) clearly shows the actual execution to be very quick, that's why I don't think it's an index problem. What do you think? Ping -- Pook-Ping Yao (He/Him) Chief Technology Officer and co-Founder Optigo Networks Inc. +1-604-897-7464 | p...@optigo.net This email, including any files attached hereto, may contain privileged or confidential information and is only for the intended addressee(s). If this email was sent to you in error, this does not constitute a waiver by Optigo Networks Inc. and we request that you kindly delete the email and notify the sender. Unauthorized use of this email is prohibited. On Mon, Dec 4, 2023 at 1:40 PM Merlin Moncure wrote: > > > On Fri, Dec 1, 2023 at 11:48 AM Ping Yao wrote: > >> Hello All. >> >> First timer here, long time user though. Thank you in advance. >> >> Can someone help me understand why my simple DELETE query is so slow to >> run? >> > > Not familiar with citus to diagnose any citus issues. Having said that, > can you confirm that all tables that have a foreign key reference to this > table have an index on the field referencing? > > For example, if you have a table customer, with a column 'customer_id', a > table 'customer_employee' might refer to the customer table if it has > 'customer_id REFERENCES customer', which would be very slow with deletes on > customer. that may not be happening here with the citus stuff, hard to > tell. Something to rule out though. > > merlin >
Re: Help understand why DELETE is so slow
On Fri, Dec 1, 2023 at 11:48 AM Ping Yao wrote: > Hello All. > > First timer here, long time user though. Thank you in advance. > > Can someone help me understand why my simple DELETE query is so slow to > run? > Not familiar with citus to diagnose any citus issues. Having said that, can you confirm that all tables that have a foreign key reference to this table have an index on the field referencing? For example, if you have a table customer, with a column 'customer_id', a table 'customer_employee' might refer to the customer table if it has 'customer_id REFERENCES customer', which would be very slow with deletes on customer. that may not be happening here with the citus stuff, hard to tell. Something to rule out though. merlin
Re: vacuumdb seems not to like option -j when run from crontab
(Sorry for top-posting. Blame gmail.) Turns out that PG 8.4.20 is also installed from the RHEL repository. Thanks. On Mon, Dec 4, 2023 at 11:13 AM Alan Hodgson wrote: > On Mon, 2023-12-04 at 11:07 -0500, Ron Johnson wrote: > > PG 9.6.24 (Yes, it's EOL.) > > When running "vacuumdb -p5433 -j4 --analyze tap_d" from a bash prompt, it > works as expected: > $ vacuumdb -p5433 -j4 --analyze tap_d > vacuumdb: vacuuming database "tap_d" > > But not when running from crontab: > vacuumdb -p5433 -j4 --analyze tap_d > vacuumdb: invalid option -- 'j' > Try "vacuumdb --help" for more information. > > Obviously I'm missing something, but don't see what it is. Attached is > the script it runs from. > > > Is your user and PATH the same? ie. are you running the same vacuumdb > executable? > >
Re: vacuumdb seems not to like option -j when run from crontab
Add a pg binary path to script br Kaido On Mon, 4 Dec 2023 at 18:13, Alan Hodgson wrote: > On Mon, 2023-12-04 at 11:07 -0500, Ron Johnson wrote: > > PG 9.6.24 (Yes, it's EOL.) > > When running "vacuumdb -p5433 -j4 --analyze tap_d" from a bash prompt, it > works as expected: > $ vacuumdb -p5433 -j4 --analyze tap_d > vacuumdb: vacuuming database "tap_d" > > But not when running from crontab: > vacuumdb -p5433 -j4 --analyze tap_d > vacuumdb: invalid option -- 'j' > Try "vacuumdb --help" for more information. > > Obviously I'm missing something, but don't see what it is. Attached is > the script it runs from. > > > Is your user and PATH the same? ie. are you running the same vacuumdb > executable? > >
Re: vacuumdb seems not to like option -j when run from crontab
On Mon, 2023-12-04 at 11:07 -0500, Ron Johnson wrote: > PG 9.6.24 (Yes, it's EOL.) > > When running "vacuumdb -p5433 -j4 --analyze tap_d" from a bash > prompt, it works as expected: > $ vacuumdb -p5433 -j4 --analyze tap_d > vacuumdb: vacuuming database "tap_d" > > But not when running from crontab: > vacuumdb -p5433 -j4 --analyze tap_d > vacuumdb: invalid option -- 'j' > Try "vacuumdb --help" for more information. > > Obviously I'm missing something, but don't see what it is. > Attached is the script it runs from. > Is your user and PATH the same? ie. are you running the same vacuumdb executable?
vacuumdb seems not to like option -j when run from crontab
PG 9.6.24 (Yes, it's EOL.)
When running "vacuumdb -p5433 -j4 --analyze tap_d" from a bash prompt, it
works as expected:
$ vacuumdb -p5433 -j4 --analyze tap_d
vacuumdb: vacuuming database "tap_d"
But not when running from crontab:
vacuumdb -p5433 -j4 --analyze tap_d
vacuumdb: invalid option -- 'j'
Try "vacuumdb --help" for more information.
Obviously I'm missing something, but don't see what it is. Attached is the
script it runs from.
#!/bin/bash
ActionLabel=VACUUM
echo; date +"%n%F %T TIMEIT $ActionLabel started."; echo
START_SECS=$(date +"%s")
set -x
vacuumdb -p5433 -j4 --analyze tap_d
set +x
FINISH_SECS=$(date +"%s")
ET=$(echo "scale=2;(${FINISH_SECS} - ${START_SECS})/60" | bc)
date +"%n%F %T TIMEIT $ActionLabel finished. Elapsed time: ${ET} minutes."
Re: libpq crashing on macOS during connection startup
> On Nov 30, 2023, at 7:53 PM, Tom Lane wrote: > >> Is there a way to simply disable GSSAPI authentication? I could not find it. > > gssencmode=disable in your connection options; but that's a tad > inconvenient probably. I discovered there is a PGGSSENCMODE environment variable. I set it to 'disable' in the environment used to run the http server. Hopefully this will solve it. https://www.postgresql.org/docs/current/libpq-envars.html John DeSoi, Ph.D.
