Re: [PERFORM] Horribly slow query/ sequential scan

2007-01-10 Thread Tom Lane
I wrote: > ... What seems to be happening is that Informix is willing to > flatten the sub-SELECT into an IN join even though the sub-SELECT is > correlated to the outer query (that is, it contains outer references). I did some googling this morning and found confirmation that recent versions of I

Re: [PERFORM] Horribly slow query/ sequential scan

2007-01-09 Thread Tom Lane
"Gregory S. Williamson" <[EMAIL PROTECTED]> writes: > As Joe indicated, there is indeed an Informix explain, appended below my > signature ... > select > w.appid,w.rate,w.is_subscribed,sum(w.hits) AS Hits ,sum(w.sius) AS IUs, > sum(w.total_amnt) AS Total,sum(w.hits) * w.rate AS ByHits, > sum(w.s

Re: [PERFORM] Horribly slow query/ sequential scan

2007-01-09 Thread Gregory S. Williamson
= informix.b.report_id NESTED LOOP JOIN 3) informix.billing_reports: SEQUENTIAL SCAN (First Row) NESTED LOOP JOIN (Semi Join) -Original Message- From: [EMAIL PROTECTED] on behalf of Plugge, Joe R. Sent: Tue 1/9/2007 7:36 AM To: pgsql-performance@postgresql.org Cc: S

Re: [PERFORM] Horribly slow query/ sequential scan

2007-01-09 Thread Plugge, Joe R.
: Re: [PERFORM] Horribly slow query/ sequential scan "Gregory S. Williamson" <[EMAIL PROTECTED]> writes: > HAving burdened others with my foolishness too often, I hesitate to > ask, but could someone either point me to a reference or explain what > the difference might be

Re: [PERFORM] Horribly slow query/ sequential scan

2007-01-09 Thread Tom Lane
"Gregory S. Williamson" <[EMAIL PROTECTED]> writes: > HAving burdened others with my foolishness too often, I hesitate to > ask, but could someone either point me to a reference or explain what > the difference might be ... I can see it with the eyes but I am having > trouble understanding what Inf

Re: [PERFORM] Horribly slow query/ sequential scan

2007-01-09 Thread Dave Cramer
, consider extending w with cols 1,2,3. regards, marcus -Ursprüngliche Nachricht- Von: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Auftrag von [EMAIL PROTECTED] Gesendet: Dienstag, 9. Januar 2007 13:36 An: Gregory S. Williamson Cc: pgsql-performance@postgresql.org Betreff: Re: [PERFORM]

Re: [PERFORM] Horribly slow query/ sequential scan

2007-01-09 Thread Gregory S. Williamson
ailto:[EMAIL PROTECTED] Sent: Tue 1/9/2007 4:50 AM To: [EMAIL PROTECTED]; Gregory S. Williamson Cc: pgsql-performance@postgresql.org Subject:AW: [PERFORM] Horribly slow query/ sequential scan Forget abount "IN". Its horribly slow. try : select w.appid, w.rate,

Re: [PERFORM] Horribly slow query/ sequential scan

2007-01-09 Thread Nörder-Tuitje , Marcus
2007 13:36 An: Gregory S. Williamson Cc: pgsql-performance@postgresql.org Betreff: Re: [PERFORM] Horribly slow query/ sequential scan I don't think I understand the idea behind this query. Do you really need billing_reports twice? > The query: > explain analyze select > w.appid,w.rate,w

Re: [PERFORM] Horribly slow query/ sequential scan

2007-01-09 Thread Gregory S. Williamson
((w.client_id)::text = '227410'::text)) Total runtime: 6.110 ms (16 rows) Thanks again (and sorry for the top-posting but this particular interface is ungainly) G -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Tue 1/9/2007 4:35 AM To: Gre

Re: [PERFORM] Horribly slow query/ sequential scan

2007-01-09 Thread db
I don't think I understand the idea behind this query. Do you really need billing_reports twice? > The query: > explain analyze select > w.appid,w.rate,w.is_subscribed,sum(w.hits) AS Hits ,sum(w.sius) AS IUs, > sum(w.total_amnt) AS Total,sum(w.hits) * w.rate AS ByHits, > sum(w.sius) * w.rate AS BY

[PERFORM] Horribly slow query/ sequential scan

2007-01-09 Thread Gregory S. Williamson
This is a query migrated from postgres. In postgres it runs about 10,000 times *slower* than on informix on somewhat newer hardware. The problem is entirely due to the planner. This PostgreSQL 8.1.4 on linux, 2 gigs of ram. The table: Table "reporting.bill_rpt_work" Column |