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 src_faktu
> 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 i
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));
C
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 a
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 m
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?
Scott Marlowe <[EMAIL PROTECTED]> writes:
> What is the difference between the two plans (i.e. explain on both boxes
> and compare)
Even more to the point, let's see EXPLAIN ANALYZE output from both boxes...
regards, tom lane
---(end of broadcast)-
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 1
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
/base//pgsql_tmp to see how large the temporary file is
during this query. I'm sure that there is large file.
AMTo:
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
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 s
11 matches
Mail list logo
