On Sun, 17 Aug 2003, Tom Lane wrote: > Stephan Szabo <[EMAIL PROTECTED]> writes: > >> with reltuples=1000 for pktest, query takes about 96 seconds > >> reltuples=10000, query takes about 15 seconds > >> reltuples=100000, query takes about 8 seconds > > > Errm, I meant in the cases where it still chose a hashed > > subplan. Stupid cold medicine. > > I'm confused too. Please explain again when you're feeling better...
Basically, the first thing I noticed was that changing reltuples on the pg_class row for a table affected the speed of explain analyze select * from othertable where foo not in (select bar from table); even when the plan wasn't changing, seqscan + filter on hashed subquery. I thought that was kind of odd since the plan didn't seem any different, but the real world time changed by about a factor of 10. Then I noted that changing sort_mem changed the point at which it would choose a hashed subquery in the initial plan based on the estimated tuples, but didn't seem to actually affect the real memory usage, which means that a table with a few million rows but reltuples still set at 1000 would eat up a very large amount of memory (in my case it sent my machine a few hundred megs into swap). ---------------------------(end of broadcast)--------------------------- TIP 8: explain analyze is your friend
