Re: Asking advice on speeding up a big table

felix(at)crowfix(dot)com wrote:
> On Tue, Apr 11, 2006 at 09:52:40AM +0200, hubert depesz lubaczewski wrote:
>
>>On 4/10/06, felix(at)crowfix(dot)com <felix(at)crowfix(dot)com> wrote:
>
>
>>>What I was hoping for was some general insight from the EXPLAIN
>>>ANALYZE, that maybe extra or different indices would help, or if there
>>>is some better method for finding one row from 100 million. I realize
>>>I am asking a vague question which probably can't be solved as
>>>presented.
>>>
>>
>>hmm .. perhaps you can try to denormalize the table, and then use
>>multicolumn indices?
>
> That's an idea ... I had thought that since my two referenced tables
> are small in comparison to the third table, that wouldn't be of any
> use, but I will give it a try. Thanks ...

You could also experiment with clustering your large tables on some
index, or using one or more partial indexes on relevant partitions of
your data set. The application of such measures really depends on how
your data behaves, so it's hard to predict whether it's going to help
you or not.

We have seen great benefits of using the 'right' index opposed to just
any index. An explain analyze showing an index scan is a good start, but
your design may yet improve.

For example, we have a table with translations of strings in different
languages. Part of that tables' contents deals with names of
geographical locations (cities mostly). An index on lowercase location
name translations and only on the translation records relevant for
locations sped up our queries immensely (from over 300ms to less than
1ms - uncached). That was a pretty amazing performance improvement to be
sure :)

So it is possible.

--
Alban Hertroys
alban(at)magproductions(dot)nl

magproductions b.v.

T: ++31(0)534346874
F: ++31(0)534346876
M:
I: www.magproductions.nl
A: Postbus 416
7500 AK Enschede

// Integrate Your World //