From: | Doug McNaught <doug(at)wireboard(dot)com> |
---|---|
To: | Joseph Shraibman <jks(at)selectacast(dot)net> |
Cc: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: max_expr_depth |
Date: | 2001-06-19 03:09:43 |
Message-ID: | m3elshb1xk.fsf@belphigor.mcnaught.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Joseph Shraibman <jks(at)selectacast(dot)net> writes:
> Doug McNaught wrote:
> >
> > If you don't, every update is its own transaction, and Postgres will
> > sync the disks (and wait for the sync to complete) after every one.
> > Doing N updates in one transaction will only sync after the whole
> > transaction is complete. Trust me; it's *way* faster.
>
> I thought WAL did away with most of the syncing.
Most but not all. It still makes a big difference. Transactions have
other overhead, too (housekeeping and such).
> Do you really think I should do 1000 updates in a transaction instead of
> an IN with 1000 items? I can do my buffer flush any way I want but I'd
> have to think the overhead of making 1000 calls to the backend would be
> more than overwhelm the cost of the big OR statement (especially if the
> server and client aren't on the same machine).
Perhaps.
The issue for me would be: OK, 1000 entries in an IN works fine.
Maybe 2000 works fine. At some point (as you've seen) you hit a
limit, whether it's query length, recursion depth or whatever. Then
you have to go rewrite your code. I like to do it right the first
time. ;)
If you know you will never ever have more than N items in the IN
clause, and N is demonstrably less than the limit, use IN. "Never
ever" is a phrase that often turns out to be false in software
devlopment...
If you're doing the updates in batches (say, 1000 at a time using IN)
you still might want to consider wrapping the whole thing in a
transaction. That way, if the client or the network craps out in the
middle of the run, you don't have a half-complete set of updates to
clean up.
-Doug
--
The rain man gave me two cures; he said jump right in,
The first was Texas medicine--the second was just railroad gin,
And like a fool I mixed them, and it strangled up my mind,
Now people just get uglier, and I got no sense of time... --Dylan
From | Date | Subject | |
---|---|---|---|
Next Message | Joseph Shraibman | 2001-06-19 03:25:09 | Re: max_expr_depth |
Previous Message | Joseph Shraibman | 2001-06-19 02:57:35 | Re: max_expr_depth |