Re: speeding up CUBE queries

I'm not sure, but probably mtree will be better ?

On Mon, 8 Oct 2007, Rajarshi Guha wrote:

> Hi, I'm running Postgres 8.2.4 and have installed the CUBE extension. Using 
> this I had a 10M row table populated with 12-dimensional zero-volume cubes 
> (i.e., 12D points).
>
> My queries are of the form
>
> select * from ctab where '(x1,x2,x3,...,x12), (y1,y2,y3,...,y12)'::cube @> 
> cubeField;
>
> So essentially I am asking for all rows that lie within the specified 
> bounding box. Now the cubeField column has a GiST index on it. As a result in 
> a number of cases I can get results in less than a minute (and in a few cases 
> under 15 sec).
>
> Now 1 minute is relatively long, but it's acceptable (but faster is always 
> better). However there are some instances when a query takes 4 to 5 minutes. 
> This is problematic, but I'm not sure I see a solution.
>
> One thing that I did observe is that the very long (4-minute) queries occur 
> when the bounding box is very densely filled with points (based on knowledge 
> of the dataset). Very fast queries occur when the bounding box is quite 
> sparsely filled. Now it is also true that the 12D space is not uniformly 
> populated, so that probably has an effect.
>
> I have altered the statistics count on the cubeField column to 100 so that 
> vacuum analyze performs larger sampling, but that doesn't seem to help the 
> timings for the more extreme queries.
>
> Could anybody point me to a strategy to improve performance of the index on 
> this dataset? I have done some searching wrt GIS databases and I found a post 
> (http://postgis.refractions.net/pipermail/postgis-users/2002-October/001526.html) 
> which talked about tuning a spatial index - but that's for 2D and it's not 
> clear whether those ideas were implemented (and if so, whether they could be 
> applied to my problem)
>
> Are there any options to improving performance on the 12D case? Or am I 
> stuck?
>
> Thanks,
>
> -------------------------------------------------------------------
> Rajarshi Guha  
> GPG Fingerprint: 0CCA 8EE2 2EEB 25E2 AB04  06F7 1BB9 E634 9B87 56EE
> -------------------------------------------------------------------
> Accuracy, n.:
> 	The vice of being right
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: explain analyze is your friend

 	Regards,
 		Oleg
_____________________________________________________________
Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru),
Sternberg Astronomical Institute, Moscow University, Russia
Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(495)939-16-83, +007(495)939-23-83