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.


Thu, 26 Sep 2002 11:17:50 +0200
"Mario Weilguni" <mario.weilguni@icomedias.com>
Re: 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
Thu, 26 Sep 2002 15:01:35 +0530
"Shridhar Daithankar" <shridhar_daithankar@persistent.co.in>
Re: [HACKERS] Performance while loading data and indexing
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.

Thu, 26 Sep 2002 19:54:48 +1000
Martijn van Oosterhout <kleptog@svana.org>
Re: [HACKERS] Performance while loading data and indexing