[PERFORM] query produces 1 GB temp file

Hi, here is a query which produces over 1G temp file in pgsql_tmp. This is on pgsql 7.4.2, RHEL 3.0, XEON MP machine with 32GB RAM, 300MB sort_mem and 320MB shared_mem. Below is the query and results for EXPLAIN and EXPLAIN ANALYZE. All tables have been analyzed before. Can some please explain

Re: [PERFORM] query produces 1 GB temp file

John A Meinel wrote: Dirk Lutzebaeck wrote: Greg Stark wrote: I gave a bunch of "explain analyze select" commands to test estimates for individual columns. What results do they come up with? If those are inaccurate then raising the statistics target is a good route. If those are

Re: [PERFORM] query produces 1 GB temp file

wrote: [EMAIL PROTECTED] (Dirk Lutzebaeck) writes: SELECT DISTINCT ON (df.val_9, df.created, df.flatid) df.docindex, df.flatobj, bi.oid, bi.en FROM bi,df WHERE bi.rc=130170467 ... ORDER BY df.val_9 ASC, df.created DESC LIMIT 1000 OFFSET 0 Just out of curiosity, what is this query supposed t

Re: [PERFORM] query produces 1 GB temp file

Hi John, thanks very much for your analysis. I'll probably need to reorganize some things. Regards, Dirk John A Meinel wrote: Dirk Lutzebaeck wrote: Hi, here is a query which produces over 1G temp file in pgsql_tmp. This is on pgsql 7.4.2, RHEL 3.0, XEON MP machine with 32GB RAM, 300MB sor

Re: [PERFORM] query produces 1 GB temp file

Greg Stark wrote: I gave a bunch of "explain analyze select" commands to test estimates for individual columns. What results do they come up with? If those are inaccurate then raising the statistics target is a good route. If those are accurate individually but the combination is inaccurate then yo

Re: [PERFORM] query produces 1 GB temp file

John, I'm doing VACUUM ANALYZE once a night. Before the tests I did VACUUM and then ANALYZE. Dirk John A Meinel wrote: Dirk Lutzebaeck wrote: Greg, Thanks for your analysis. But I dont get any better after bumping STATISTICS target from 10 to 200. explain analyze shows that the optimiz

Re: [PERFORM] query produces 1 GB temp file

--- Seq Scan on bi (cost=0.00..41078.76 rows=603988 width=53) (actual time=0.021..3692.238 rows=945487 loops=1) Filter: (co = 117305223::oid) Total runtime: 5786.268 ms (3 rows) Greg Stark wrote: Dirk Lutzebaeck <[EMAIL PROTECTED]> writes: Below is the query and results for EXPLA

[PERFORM] query produces 1 GB temp file

Hi, here is a query which produces over 1G temp file in pgsql_tmp. This is on pgsql 7.4.2, RHEL 3.0, XEON MP machine with 32GB RAM, 300MB sort_mem and 320MB shared_mem. Below is the query and results for EXPLAIN and EXPLAIN ANALYZE. All tables have been analyzed before. Can some please explain

Re: [PERFORM] Toooo many context switches (maybe SLES8?)

Could this be related to the O(1) scheduler backpatches from 2.6 to 2.4 kernel on newer 2.4er distros (RedHat, SuSE)? Tom Lane wrote: Joe Conway <[EMAIL PROTECTED]> writes: Improve spinlock code for recent x86 processors: insert a PAUSE instruction in the s_lock() wait loop, and use test befo

Re: [PERFORM] Wierd context-switching issue on Xeon

I would agree to Tom, that too much parameters are involved to blame bigmem. I have access to the following machines where the same application operates: a) Dual (4way) XEON MP, bigmem, HT off, ServerWorks chipset (a Fujitsu-Siemens Primergy) performs ok now because missing indexes were added