Re: [SQL] Very slow updates when using IN syntax subselect
Bryce Nesbitt <[EMAIL PROTECTED]> writes: > Tom Lane wrote: >> What does EXPLAIN show for this and for the base query? >-> Seq Scan on event (cost=0.00..0.00 rows=1 width=408) > Filter: (reconciled = false) > select count(*) from event; > --- > 116226 It seems pretty clear that you've never vacuumed nor analyzed these tables ... else the planner would have some clue about their sizes. Do that and then see what you get. regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] Very slow updates when using IN syntax subselect
Tom Lane wrote: > Bryce Nesbitt <[EMAIL PROTECTED]> writes: > >> Tom Lane wrote: >> >>> What does EXPLAIN show for this and for the base query? >>> > > >>-> Seq Scan on event (cost=0.00..0.00 rows=1 width=408) >> Filter: (reconciled = false) >> > > >> select count(*) from event; >> --- >> 116226 >> > > It seems pretty clear that you've never vacuumed nor analyzed these > tables ... else the planner would have some clue about their sizes. > Do that and then see what you get. > They occur in fine time. That's good, thanks. But jeeze, can't postgres figure this out for itself? ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] Very slow updates when using IN syntax subselect
Bryce Nesbitt wrote: > > It seems pretty clear that you've never vacuumed nor analyzed these > > tables ... else the planner would have some clue about their sizes. > > Do that and then see what you get. > > They occur in fine time. That's good, thanks. But jeeze, can't > postgres figure this out for itself? I'm sure you wouldn't appreciate it if PostgreSQL did a full table scan before each query to figure out the total size of the involved tables. -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Very slow updates when using IN syntax subselect
Peter Eisentraut wrote: > Bryce Nesbitt wrote: > >>> It seems pretty clear that you've never vacuumed nor analyzed these >>> tables ... else the planner would have some clue about their sizes. >>> Do that and then see what you get. >>> >> They occur in fine time. That's good, thanks. But jeeze, can't >> postgres figure this out for itself? >> > > I'm sure you wouldn't appreciate it if PostgreSQL did a full table scan > before each query to figure out the total size of the involved tables. > Nope. But let's say the query optimizer thought the table had one row. The the query starts, and 111,000 rows later... It seems that a mismatch between the static table size, and the actual one counted as you go, would be a quick check. That could set a flag for later background processing -Bryce ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] Very slow updates when using IN syntax subselect
Peter Eisentraut <[EMAIL PROTECTED]> writes: > Bryce Nesbitt wrote: >> They occur in fine time. That's good, thanks. But jeeze, can't >> postgres figure this out for itself? > I'm sure you wouldn't appreciate it if PostgreSQL did a full table scan > before each query to figure out the total size of the involved tables. It's also less than polite to complain about the behavior of two-year-old releases, without making any effort to ascertain whether more-current versions are smarter. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
