From: | "NbForYou" <nbforyou(at)hotmail(dot)com> |
---|---|
To: | <pgsql-performance(at)postgresql(dot)org> |
Subject: | Process Time X200 |
Date: | 2006-03-10 07:11:44 |
Message-ID: | BAY107-DAV18040C8CE1ECE56E895A9EDBED0@phx.gbl |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Michael Fuhr | 2006-03-10 08:59:43 | Re: Process Time X200 |
Previous Message | Casey Allen Shobe | 2006-03-10 02:25:08 | Using materialized views for commonly-queried subsets |