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

Re: Speeding up query, Joining 55mil and 43mil records.

From: Sven Geisler <sgeisler(at)aeccom(dot)com>
To: nicky <nicky(at)valuecare(dot)nl>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: Speeding up query, Joining 55mil and 43mil records.
Date: 2006-06-22 11:29:41
Message-ID: 449A7F25.3040607@aeccom.com (view raw or flat)
Thread:
Lists: pgsql-performance
Hi Nicky,

Did you tried to create an index to avoid the sequential scans?

Seq Scan on src_faktuur_verrsec t0...

I think, you should try

CREATE INDEX src.src_faktuur_verrsec_codesubstr ON 
src.src_faktuur_verrsec (substr(src.src_faktuur_verrsec.code,1,2))

Cheers
Sven.

nicky schrieb:
> Hello again,
> 
> thanks for all the quick replies.
> 
> It seems i wasn't entirely correct on my previous post, i've mixed up 
> some times/numbers.
> 
> Below the correct numbers
> 
> MSSQL:      SELECT COUNT(*) from JOIN (without insert)   17 minutes
> PostgreSQL: SELECT COUNT(*) from JOIN (without insert)   33 minutes
> PostgreSQL: complete query                               55 minutes

  <snip snip snip>
> 
> A lot of improvement also in the select count: 33 minutes vs 10 minutes.
> 
> 
> To us, the speeds are good. Very happy with the performance increase on 
> that select with join, since 90% of the queries are SELECT based.
> 
> The query results in 7551616 records, so that's about 4500 inserts per 
> second. I'm not sure if that is fast or not. Any further tips would be 
> welcome.

In response to

Responses

pgsql-performance by date

Next:From: nickyDate: 2006-06-22 12:10:50
Subject: Re: Speeding up query, Joining 55mil and 43mil records.
Previous:From: nickyDate: 2006-06-22 09:48:45
Subject: Re: Speeding up query, Joining 55mil and 43mil records.

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