Re: PG for DataWarehouse type Queries

Can anyone shed some light on this. I just would like to know if queries
for raw data (not aggregregates) is expected to take a long time.
Running times between 30 - 2 hours for large dataset pulls.

Involves lots of joins on very large tables (min 1 millon rows each
table, 300 columns per table)

Joins are done in the form of Left joins (sometimes on the same tables,
due to normalisation)

Is 30min - 2hours too long or is this considered "normal"??

On 8/3/07, Ow Mun Heng <Ow(dot)Mun(dot)Heng(at)wdc(dot)com> wrote:
> Can anyone shed some light on this. I just would like to know if queries
> for raw data (not aggregregates) is expected to take a long time.
> Running times between 30 - 2 hours for large dataset pulls.
>
> Involves lots of joins on very large tables (min 1 millon rows each
> table, 300 columns per table)
>
> Joins are done in the form of Left joins (sometimes on the same tables,
> due to normalisation)
>
> Is 30min - 2hours too long or is this considered "normal"??

That depends entirely on your query, your dataset, the machine you're
using, etc. Your best bet is probably to post an EXPLAIN ANALYZE of
your query along with some description of what's going on and what
you're running this all on (pg version, machine specs, etc.) and see
if someone can come up with an acceptable way to make the query
faster.

- Josh

Ow Mun Heng <Ow(dot)Mun(dot)Heng(at)wdc(dot)com> writes:
> Can anyone shed some light on this. I just would like to know if queries
> for raw data (not aggregregates) is expected to take a long time.
> Running times between 30 - 2 hours for large dataset pulls.

> Involves lots of joins on very large tables (min 1 millon rows each
> table, 300 columns per table)

> Joins are done in the form of Left joins (sometimes on the same tables,
> due to normalisation)

> Is 30min - 2hours too long or is this considered "normal"??

This question is nearly content-free. How many joins is "lots"? How
many result rows are you expecting? What PG version are you using?
What have you got work_mem set to? What does EXPLAIN say the plan is
(EXPLAIN ANALYZE output would be better, but you'll have to wait for the
results...)?

regards, tom lane

Ow Mun Heng escreveu:
> Can anyone shed some light on this. I just would like to know if queries
> for raw data (not aggregregates) is expected to take a long time.
> Running times between 30 - 2 hours for large dataset pulls.
>
> Involves lots of joins on very large tables (min 1 millon rows each
> table, 300 columns per table)
>
> Joins are done in the form of Left joins (sometimes on the same tables,
> due to normalisation)
>
> Is 30min - 2hours too long or is this considered "normal"??

What do you mean for 'Datawarehouse queries' ?
Is there any OLAP server between youp app and Postrgres?

--
[]´s,

André Volpato
ECOM Tecnologia Ltda
andre(dot)volpato(at)ecomtecnologia(dot)com(dot)br
(41) 3014 2322

On Fri, 2007-08-03 at 15:12 +0800, Ow Mun Heng wrote:

> Is 30min - 2hours too long or is this considered "normal"??

Yes.

--
Simon Riggs
EnterpriseDB http://www.enterprisedb.com

"Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us> writes:

> Ow Mun Heng <Ow(dot)Mun(dot)Heng(at)wdc(dot)com> writes:
>> Can anyone shed some light on this. I just would like to know if queries
>> for raw data (not aggregregates) is expected to take a long time.
>> Running times between 30 - 2 hours for large dataset pulls.
>
>> Involves lots of joins on very large tables (min 1 millon rows each
>> table, 300 columns per table)

One more question to pile on to the others: what version of Postgres is this.

I seem to recall some older versions had trouble with tables with hundreds of
columns, but that's just guessing in the dark without actual facts to look at.

--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com

On Fri, 2007-08-03 at 07:55 -0600, Josh Tolley wrote:
> On 8/3/07, Ow Mun Heng <Ow(dot)Mun(dot)Heng(at)wdc(dot)com> wrote:
> > Can anyone shed some light on this. I just would like to know if
> queries
> > for raw data (not aggregregates) is expected to take a long time.
> > Running times between 30 - 2 hours for large dataset pulls.
> >
> > Involves lots of joins on very large tables (min 1 millon rows each
> > table, 300 columns per table)
> >
> > Joins are done in the form of Left joins (sometimes on the same tables,
> > due to normalisation)
> >
> > Is 30min - 2hours too long or is this considered "normal"??
>
> That depends entirely on your query, your dataset, the machine you're
> using, etc. Your best bet is probably to post an EXPLAIN ANALYZE of
> your query along with some description of what's going on and what
> you're running this all on (pg version, machine specs, etc.) and see
> if someone can come up with an acceptable way to make the query
> faster.

Sorry this generated more questions. I was vague cos it wasn't really
running on PG (yet)

Actual query is running on SQL Server. I'm considering offloading some
of those queries to a PG (i have 8.2.4 running for testing purposes)
server. (Very Low end desktop whitebox spec, eg: 1G Ram, 3x500GB drives
raid 1/1+0/0+1, PIV 2G, with the tables Pre-joined and de-normalised
every say 5 min and perform table partitioning based on date ranges)

Current queries in SQL server takes between 30min to 2 hours. (4G
mem/Quad Dual code Xeon)

Queries involves 11 Joins (of 5 Main tables, of which 3 are Large, 2 are
fact tables)

large tables has > 1min rows, fact ~max 200 rows)

Query is of type

Select
A.A,
A.B,
C.A,
D.D
from
(Select
...
)A
left outer join
(select ...
...
)B
on A.A = B.A
let outer join
(select
...
)C

and so on..

I'm hoping that by pre-joining them and using table partitioning, the
performance would greatly increase. (I'm still testing)

BTW, this question is just to have a feel if it's "normal" to have such
long running queries (time) for ETL type queries.

Sorry for the noise (since this is not really PG related yet)
I'm still researching the feasibility etc.

Ow Mun Heng <Ow(dot)Mun(dot)Heng(at)wdc(dot)com> writes:
> Queries involves 11 Joins (of 5 Main tables, of which 3 are Large, 2 are
> fact tables)

It would be a good idea to increase join_collapse_limit to more than 11.
This will increase the planning time, but the investment will likely be
worthwhile for such a large query.

regards, tom lane