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
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
Hello Sven,
We have the following indexes on src_faktuur_verrsec
/
CREATE INDEX src_faktuur_verrsec_idx0
ON src.src_faktuur_verrsec
USING btree
(id);
CREATE INDEX src_faktuur_verrsec_idx1
ON src.src_faktuur_verrsec
USING btree
(substr(code::text, 1, 2));
PostgreSQL elects not to use them. I assume, because it most
likely needs to traverse the entire table anyway.
if i change: / substr(t0.code,1,2) not in
('14','15','16','17')/
to (removing the NOT): /substr(t0.code,1,2) in
('14','15','16','17')/
it uses the index,
Hi Nick,
I'm not that good to advice how to get PostgreSQL to use an index to get
your results faster.
Did you try not (substr(t0.code,1,2) in ('14','15','16','17'))?
Cheers
Sven.
nicky schrieb:
Hello Sven,
We have the following indexes on src_faktuur_verrsec
/
CREATE INDEX
Hello People,
I'm trying to solve a 'what i feel is a'
performance/configuration/query error on my side. I'm fairly new to
configuring PostgreSQL so, i might be completely wrong with my
configuration.
My database consists of 44 tables, about 20GB. Two of those tables are
'big/huge'. Table
:
pgsql-performance@postgresql.orgSubject: [PERFORM] Speeding up
query, Joining 55mil and 43mil records. Hello People, I'm trying to solve a 'what i feel is a'
performance/configuration/query error on my side. I'm fairly new to
configuring PostgreSQL so, i might be completely wrong with my
Hi Nicky,
I guess, you should try to upgrade the memory setting of PostgreSQL first.
work_mem = 65536
Is a bit low for such large joins.
Did you get a change to watch the directory
PGDATA/base/DBOID/pgsql_tmp to see how large the temporary file is
during this query. I'm sure that there is
On Wed, 2006-06-21 at 08:47, nicky wrote:
Hello People,
SNIPPAGE
The query above takes around 42 minutes.
However, i also have a wimpy desktop machine with 1gb ram. Windows
with MSSQL 2000 (default installation), same database structure, same
indexes, same query, etc and it takes 17
On Wed, Jun 21, 2006 at 03:47:19PM +0200, nicky wrote:
WHERE substr(t0.code,1,2) not in ('14','15','16','17')
AND (substr(t0.correctie,4,1) '1' OR t0.correctie is null)
AND EXTRACT(YEAR from t1.datum) 2004;
How much data do you expect to be getting back from that where clause?
10 matches
Mail list logo
