From: | Scott Marlowe <smarlowe(at)g2switchworks(dot)com> |
---|---|
To: | Yudie Gunawan <yudiepg(at)gmail(dot)com> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Table partition for very large table |
Date: | 2005-03-28 19:23:27 |
Message-ID: | 1112037807.22988.0.camel@state.g2switchworks.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On Mon, 2005-03-28 at 13:02, Yudie Gunawan wrote:
> I actualy need to join from 2 tables. Both of them similar and has
> more than 4 millions records.
>
> CREATE TABLE prdt_old (
> groupnum int4 NOT NULL,
> sku varchar(30) NOT NULL,
> url varchar(150),
> );
>
> CREATE TABLE prdt_new(
> groupnum int4 NOT NULL,
> sku varchar(30) NOT NULL,
> url varchar(150) NOT NULL,
> );
>
> The query returns group number and sku from old table where has no url
> in prdt_new table.
>
> INSERT into prdtexpired
> SELECT pn.groupnum, pn.sku
> FROM prdt_old po
> LEFT OUTER JOIN prdt_new pn
> ON (pn.groupnum = po.groupnum and pn.sku = po.sku)
> WHERE pn.url is null or pn.url= '';
>
> I already have resolution for this problem where I seperate the query
> for each group.
>
> But when I address this question, I hope that Postgresql has some kind
> of table optimazion for large records. Based my experience it is
> faster to query from chopped smaller table rather than query from
> single huge table. I heard Oracle has some kind of table partition
> that acts like single table.
Hold on, let's diagnose the real problem before we look for solutions.
What does explain <query> tell you? Have you analyzed the database?
What are your postgresql.conf settings?
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2005-03-28 19:25:14 | Re: Can't pg_dumpall, claims database exists twice |
Previous Message | Kevin Murphy | 2005-03-28 19:13:55 | Re: LWM 2004 Readers' Choice Nomination |