Thread: Re: [HACKERS] Performance while loading data and indexing

On 26 Sep 2002 at 11:17, Mario Weilguni wrote:

> On 26 Sep 2002 at 14:05, Shridhar Daithankar wrote:
> > Some time back I posted a query to build a site with 150GB of database. In
> last
> > couple of weeks, lots of things were tested at my place and there are some
> > results and again some concerns.
>
> > 2) Creating index takes huge amount of time.
> > Load time: 14581 sec/~8600 rows persec/~ an MB of data per sec.
> > Create unique composite index on 2 char and a timestamp field:  25226 sec.
> > Database size on disk: 26GB
> > Select query: 1.5 sec. for approx. 150 rows.
>
> I never tried 150GB of data, but 10GB of data, and this worked fine for me.
> Maybe it will help if you post your table schema, including which indexes you
> use, and the average size of one tuple.

Well the test runs were for 10GB of data. Schema is attached. Read in fixed
fonts..Last nullable fields are dummies but may be used in fututre and varchars
are not acceptable(Not my requirement). Tuple size is around 100 bytes..

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..

Bye
 Shridhar

--
brain, v: [as in "to brain"]    To rebuke bluntly, but not pointedly; to dispel a
source    of error in an opponent.        -- Ambrose Bierce, "The Devil's Dictionary"


Field Name    Field Type    Nullable    Indexed
type        int        no        no
esn        char (10)    no        yes
min        char (10)    no        yes
datetime    timestamp    no        yes
opc0        char (3)    no        no
opc1        char (3)    no        no
opc2        char (3)    no        no
dpc0        char (3)    no        no
dpc1        char (3)    no        no
dpc2        char (3)    no        no
npa        char (3)    no        no
nxx        char (3)    no        no
rest        char (4)    no        no
field0        int        yes        no
field1        char (4)    yes        no
field2        int        yes        no
field3        char (4)    yes        no
field4        int        yes        no
field5        char (4)    yes        no
field6        int        yes        no
field7        char (4)    yes        no
field8        int        yes        no
field9        char (4)    yes        no

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@svana.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.