Re: Speeding up query, Joining 55mil and 43mil records. - Mailing list 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.