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
Views: Raw Message | Whole Thread | Download mbox | Resend email
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

Browse pgsql-performance by date

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