Skip site navigation (1) Skip section navigation (2)

Re: [HACKERS] Performance while loading data and indexing

From: Martijn van Oosterhout <kleptog(at)svana(dot)org>
To: Shridhar Daithankar <shridhar_daithankar(at)persistent(dot)co(dot)in>
Cc: pgsql-hackers(at)postgresql(dot)org, pgsql-general(at)postgresql(dot)org
Subject: Re: [HACKERS] Performance while loading data and indexing
Date: 2002-09-26 09:54:48
Message-ID: 20020926095448.GC10471@svana.org (view raw or flat)
Thread:
Lists: pgsql-generalpgsql-hackers
On Thu, Sep 26, 2002 at 03:01:35PM +0530, Shridhar Daithankar wrote:
Content-Description: Mail message body
> The index creation query was
> 
> CREATE INDEX index1 ON tablename (esn,min,datetime);
> 
> What if I put datetime ahead? It's likely the the datetime field will have high 
> degree of locality being log data..

The order of fields depends on what you're using it for. For example, you
can use the above index for a query using the conditions:

esn = 'aaa' 
esn = 'bbb' and min = 'xxx'

but not for queries with only

datetime = '2002-09-26'
min = 'ddd' and datetime = '2002-10-02'

The fields can only be used left to right. This is where a single
multicolumn index differs from multiple indexes of different columns.

Have you used EXPLAIN ANALYSE to determine whether your indexes are being
used optimally?

-- 
Martijn van Oosterhout   <kleptog(at)svana(dot)org>   http://svana.org/kleptog/
> There are 10 kinds of people in the world, those that can do binary
> arithmetic and those that can't.

In response to

pgsql-hackers by date

Next:From: Patrick WelcheDate: 2002-09-26 09:55:09
Subject: Re: Relation 0 does not exist
Previous:From: Mario WeilguniDate: 2002-09-26 09:50:08
Subject: Re: Performance while loading data and indexing

pgsql-general by date

Next:From: Patrick WelcheDate: 2002-09-26 09:55:09
Subject: Re: Relation 0 does not exist
Previous:From: Justin CliftDate: 2002-09-26 09:49:53
Subject: Re: [HACKERS] Performance while loading data and indexing

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group