Process Time X200

["NbForYou" <nbforyou@xxxxxxxxxxx>]

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