Subject: Re: Speedup hint needed, if available? :)
From: Mario Splivalo <mario.splivalo@mobart.hr>
Reply-To: mario.splivalo@mobart.hr
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-performance@postgresql.org
In-Reply-To: <23931.1149001555@sss.pgh.pa.us>
References: <1148998681.18422.24.camel@localhost.localdomain>
	<23931.1149001555@sss.pgh.pa.us>
Content-Type: text/plain
Organization: Mob-Art
Date: Tue, 30 May 2006 17:16:40 +0200
Message-Id: <1149002200.18422.41.camel@localhost.localdomain>
Mime-Version: 1.0
Content-Transfer-Encoding: 7bit

On Tue, 2006-05-30 at 11:05 -0400, Tom Lane wrote:

> Do you have realistic test data?  The EXPLAIN shows that this is pulling
> 275366 of the 826032 rows in the two tables, which seems like rather a
> lot for a single user.  If it's reasonable that the query needs to fetch
> one-third of the data, then you should resign yourself to it taking
> awhile :-(

I'd say so, yes. The user_subscription table now has only six rows, but
the number of actions (giving/taking credits) for a user could easily be
as high as 50.000. 

> If the expected number of matching rows were much smaller, it would
> make sense to use indexscans over the two big tables, but unfortunately
> existing PG releases don't know how to generate an indexscan join
> with a UNION ALL in between :-(.  FWIW, 8.2 will be able to do it.
> In current releases the only thing I can suggest is to merge
> user_subscription_credits_given and user_subscription_credits_taken
> into one table so you don't need the UNION ALL.

See, that's an idea! :) Thnx, I'll try that.

Is it inapropriate to ask about rough estimate on availableness of
8.2? :)

	Mario
-- 
Mario Splivalo
Mob-Art
mario.splivalo@mobart.hr

"I can do it quick, I can do it cheap, I can do it well. Pick any two."