Process Time X200 - Mailing list pgsql-performance
| From | NbForYou |
|---|---|
| Subject | Process Time X200 |
| Date | |
| Msg-id | BAY107-DAV18040C8CE1ECE56E895A9EDBED0@phx.gbl Whole thread Raw |
| Responses |
Re: Process Time X200
|
| List | 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 youpgsql-performance by date: