 | |
Re: [BUGS] BUG #1541: Unusually long INSERT times after fresh
mailing.database.pgsql-bugs mailing.database.pgsql-bugs
 |
| | Thread Tools | Display Modes |
#1
 
| |||
| |||
|
Re: [BUGS] BUG #1541: Unusually long INSERT times after fresh -
03-19-2005
, 10:22 AM
|
Andrew - Supernews <andrew+nonews (AT) supernews (DOT) com> writes: It turns out that the scenario above is trivial to hit in 8.0 using referential constraints; RI triggers cache their plans, and on 8.0 the RI query is planned as a seqscan if the tables are freshly created. (On 7.4 the plan is an index scan, thanks to the default 1000 rows / 10 pages stats.) Hm. One thing we could do is to throw in some default values when we see the table has exactly zero pages --- perhaps ye olde traditional 1000/10, or possibly something else, but anyway not exactly 0/0. The reason I thought we didn't need to do this sort of hack anymore is that pg_dump loads the tables first and then creates the RI constraints. What exactly is the common case where the wrong thing happens? |
turn off the constraint (as I believe that's non-default on data-only
dumps now).
---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend
#2
| |||
| |||
|
|
On Fri, 18 Mar 2005, Tom Lane wrote: Hm. One thing we could do is to throw in some default values when we see the table has exactly zero pages --- perhaps ye olde traditional 1000/10, or possibly something else, but anyway not exactly 0/0. |
Quote:
|
The reason I thought we didn't need to do this sort of hack anymore is that pg_dump loads the tables first and then creates the RI constraints. What exactly is the common case where the wrong thing happens? Probably loading a schema only dump followed by a data load that doesn't turn off the constraint (as I believe that's non-default on data-only dumps now). |
(the bug report for which I guess is in the moderation queue somewhere)
were examples of data imports being done at the application level. i.e.
the application is taking data from some external source and loading its
tables as it would in normal usage. In the most recent example this
amounted to under 200k rows, which was taking about 3 minutes to process
on 7.4.x (clearly a reasonable time) and more than 90 minutes on 8.0.1
(clearly not reasonable).
I know on the irc channel there have been at least a couple of reports of
slowness on restoring data-only dumps, but I haven't been able to pin that
down to the same problem (especially since such a restore should be filling
the referenced table before the referencing table).
--
Andrew, Supernews
http://www.supernews.com - individual and corporate NNTP services
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
|
« Previous Thread
|
Next Thread »
| Thread Tools | |
| Display Modes | |
Linear Mode
| |
Powered by vBulletin Version 3.5.3
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.