Re: Speedup hint needed, if available? :)

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(dot)splivalo(at)mobart(dot)hr

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