|
Hello,
I could need some help.
I have a Postgresql
database
When i do a query on my homeserver the result is given
back fast but when i do the same query on my webhost server the query is useless
because of the processtime (200 times slower
(56366.20 / 281.000 = 200.59) ). My Pc
is just a simple pc in reference to the high quality systems my webhost
uses.
I have included the query plan and the
table
Query:
explain analyze SELECT B.gegevensnaam AS boss, E.gegevensnaam
FROM
nieuw_gegevens AS E
LEFT OUTER JOIN
nieuw_gegevens AS B
ON B.lft
= (SELECT MAX(lft)
FROM nieuw_gegevens AS S
WHERE E.lft > S.lft
AND E.lft < S.rgt) order by boss, gegevensnaam
On
the WEBHOST:
QUERY PLAN
Sort (cost=1654870.86..1654871.87 rows=403 width=38) (actual
time=56365.13..56365.41 rows=403 loops=1)
Sort Key:
b.gegevensnaam, e.gegevensnaam
-> Nested
Loop (cost=0.00..1654853.42 rows=403 width=38) (actual
time=92.76..56360.79 rows=403 loops=1)
Join Filter: ("inner".lft =
(subplan))
-> Seq Scan on
nieuw_gegevens e (cost=0.00..8.03 rows=403 width=19) (actual
time=0.03..1.07 rows=403 loops=1)
-> Seq Scan on
nieuw_gegevens b (cost=0.00..8.03 rows=403 width=19) (actual
time=0.00..0.79 rows=403 loops=403)
SubPlan
-> Aggregate (cost=10.16..10.16
rows=1 width=4) (actual time=0.34..0.34 rows=1 loops=162409)
-> Seq
Scan on nieuw_gegevens s (cost=0.00..10.04 rows=45 width=4) (actual
time=0.20..0.33 rows=2 loops=162409)
Filter:
(($0 > lft) AND ($0 < rgt))
Total runtime: 56366.20 msec
11
row(s)
Total runtime: 56,370.345 ms
On my HOMESERVER:
QUERY PLAN
Sort (cost=12459.00..12461.04 rows=813
width=290) (actual time=281.000..281.000 rows=403 loops=1)
Sort
Key: b.gegevensnaam, e.gegevensnaam
-> Merge Left
Join (cost=50.94..12419.71 rows=813 width=290) (actual
time=281.000..281.000 rows=403 loops=1)
Merge Cond:
("outer"."?column3?" = "inner".lft)
-> Sort (cost=25.47..26.48
rows=403 width=149) (actual time=281.000..281.000 rows=403 loops=1)
Sort
Key: (subplan)
-> Seq
Scan on nieuw_gegevens e (cost=0.00..8.03 rows=403 width=149) (actual
time=0.000..281.000 rows=403 loops=1)
SubPlan
-> Aggregate (cost=10.16..10.16
rows=1 width=4) (actual time=0.697..0.697 rows=1 loops=403)
-> Seq
Scan on nieuw_gegevens s (cost=0.00..10.05 rows=45 width=4) (actual
time=0.308..0.658 rows=2 loops=403)
Filter:
(($0 > lft) AND ($0 < rgt))
-> Sort (cost=25.47..26.48
rows=403 width=149) (actual time=0.000..0.000 rows=770 loops=1)
Sort
Key: b.lft
-> Seq
Scan on nieuw_gegevens b (cost=0.00..8.03 rows=403 width=149) (actual
time=0.000..0.000 rows=403 loops=1)
Total runtime: 281.000 ms
15
row(s)
Total runtime: 287.273 ms
As you can see the
query isn't useful anymore because of the processtime. Please Also
notice that both systems use a different query plan.
Also on the webhost we have a loop of 162409 (403 rows * 403 rows).
Both systems also use a different postgresql version. But I cannot believe
that the performance difference between 1 version could be this big regarding
self outer join queries!
Table
CREATE TABLE nieuw_gegevens
(
gegevensid int4 NOT NULL DEFAULT
nextval('nieuw_gegevens_gegevensid_seq'::text),
gegevensnaam
varchar(255) NOT NULL,
lft int4 NOT NULL,
rgt
int4 NOT NULL,
keyword text,
CONSTRAINT
nieuw_gegevens_pkey PRIMARY KEY (gegevensid),
CONSTRAINT
nieuw_gegevens_gegevensnaam_key UNIQUE (gegevensnaam)
)
WITH OIDS;
Does anyone now how to resolve this problem? Could it be that the
configuration of the webhost postgresql could me wrong?
thank you
|
- [PERFORM] Process Time X200 NbForYou
-
