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

Re: Looking for ideas on how to speed up warehouse loading

From: CoL <col(at)mportal(dot)hu>
To: pgsql-performance(at)postgresql(dot)org
Subject: Re: Looking for ideas on how to speed up warehouse loading
Date: 2004-04-22 23:05:20
Message-ID: c69j1e$27jc$1@news.hub.org (view raw or flat)
Thread:
Lists: pgsql-performance
hi,

Sean Shanny wrote, On 4/22/2004 23:56:
> 
> SELECT t1.id, t2.url FROM referral_temp t2 LEFT OUTER JOIN d_referral t1 
> ON t2.url = t1.referral_raw_url ORDER BY t1.id

index on url (text) has no sense. Try to use and md5 (char(32) column) 
which contains the md5 hash of url field. and join these ones. You can 
have a better index on this char 32 field.

do not forget to analyze the tables after data load, and you can fine 
tune you postgresql.conf, default_statistics_target for better index 
info, and others.
check this info pages:
http://www.varlena.com/varlena/GeneralBits/Tidbits/annotated_conf_e.html
http://www.varlena.com/varlena/GeneralBits/Tidbits/perf.html

C.

In response to

pgsql-performance by date

Next:From: Sean ShannyDate: 2004-04-22 23:30:53
Subject: Re: Looking for ideas on how to speed up warehouse loading
Previous:From: Manfred KoizarDate: 2004-04-22 22:50:37
Subject: Re: Setting Shared Buffers , Effective Cache, Sort Mem Parameters

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