Re: test / live environment, major performance difference - Mailing list pgsql-performance

Fast:

"public";"layertype";"id";0;4;-1;"";"";"{1,442,508,575,641,708,774,840,907,973,1040}";0.999995

"public";"layertype";"label";0;14;-0.971429;"{arch,bank,bench,canyon,gap,hill,hills,levee,mountain,mountains}";"{0.00300752,0.00300752,0.00300752,0.00300752,0.00300752,0.00300752,0.00300752,0.00300752,0.00300752,0.00300752}";"{"abandoned
airfield",boatyard,corridor,forest(s),"intermittent lake","metro
station","park headquarters",reefs,"section of bank",swamp,zoo}";0.107307
"public";"layertype";"parentid";0.98797;4;2;"{4,1}";"{0.00902256,0.00300752}";"";-0.142857
"public";"layertype";"zorder";0;4;9;"{0}";"{0.98797}";"{1,2,3,4,5,6,7,8}";0.928955
"public";"layertype";"description";0.100752;74;-0.888722;"{"a branch of
a canyon or valley","a low, isolated, rounded hill","a near-level
shallow, natural depression or basin, usually containing an intermittent
lake, pond, or pool","a relatively shallow, wide depression, the bottom
of which usually has a continuous gradient","a shore zone of coarse
unconsolidated sediment that extends from the low-water line to the
highest reach of storm waves","a surface-navigation hazard composed of
consolidated material","a surface-navigation hazard composed of
unconsolidated
material"}";"{0.00300752,0.00300752,0.00300752,0.00300752,0.00300752,0.00300752,0.00300752}";"{"a
barrier constructed across a stream to impound water","a comparatively
depressed area on an icecap","a facility for pumping oil through a
pipeline","a large house, mansion, or chateau, on a large estate","an
area drained by a stream","an elongate (tongue-like) extension of a flat
sea floor into an adjacent higher feature","a place where caravans stop
for rest","a series of associated ridges or seamounts","a sugar mill no
longer used as a sugar mill","bowl-like hollows partially surrounded by
cliffs or steep slopes at the head of a glaciated
valley","well-delineated subdivisions of a large and complex positive
feature"}";-0.0178932

"public";"layertype";"code";0.0135338;9;-1;"";"";"{A.ADM1,H.HBRX,H.STMM,L.RGNL,S.BUSTN,S.HTL,S.PKLT,S.TRIG,T.MTS,U.GAPU,V.VINS}";0.995628

Slow:

"public";"layertype";"id";0;4;-1;"";"";"{1,437,504,571,638,705,772,839,906,973,1040}";-0.839432

"public";"layertype";"label";0;15;-0.965723;"{arch,bank,bench,canyon,country,gap,hill,hills,levee,mountain}";"{0.00298063,0.00298063,0.00298063,0.00298063,0.00298063,0.00298063,0.00298063,0.00298063,0.00298063,0.00298063}";"{"abandoned
airfield",boatyard,"cotton plantation",fork,"intermittent oxbow
lake","military installation","park headquarters",reef,"second-order
administrative division",swamp,zoo}";-0.0551452
"public";"layertype";"parentid";0.00745157;4;7;"{300}";"{0.976155}";"{1,1,4,5,8,12}";0.92262
"public";"layertype";"zorder";0;4;8;"{0}";"{0.971684}";"{1,2,3,3,5,7,7}";0.983028
"public";"layertype";"description";0.110283;74;-0.879285;"{"a branch of
a canyon or valley","a low, isolated, rounded hill","a near-level
shallow, natural depression or basin, usually containing an intermittent
lake, pond, or pool","a relatively shallow, wide depression, the bottom
of which usually has a continuous gradient","a shore zone of coarse
unconsolidated sediment that extends from the low-water line to the
highest reach of storm waves","a surface-navigation hazard composed of
consolidated material","a surface-navigation hazard composed of
unconsolidated
material"}";"{0.00298063,0.00298063,0.00298063,0.00298063,0.00298063,0.00298063,0.00298063}";"{"a
barrier constructed across a stream to impound water","a comparatively
depressed area on an icecap","a facility for pumping water from a major
well or through a pipeline","a large inland body of standing water","an
area drained by a stream","an embankment bordering a canyon, valley, or
seachannel","a place where diatomaceous earth is extracted","a series of
associated ridges or seamounts","a sugar mill no longer used as a sugar
mill","bowl-like hollows partially surrounded by cliffs or steep slopes
at the head of a glaciated valley","well-delineated subdivisions of a
large and complex positive feature"}";0.0103485

"public";"layertype";"code";0.023845;9;-1;"";"";"{A.ADM1,H.INLT,H.STMM,L.RNGA,S.BUSTN,S.HUT,S.PKLT,S.TRIG,T.MTS,U.GAPU,V.VINS}";-0.852108

This table contains identical data.

Thanx for your help Tom




Tom Lane wrote:
> Christo Du Preez <christo@mecola.com> writes:
>
>> Yes, I have just about tried every combination of vacuum on the
>> database. Just to make 100% sure.
>>
>
> Well, there's something mighty wacko about that rowcount estimate;
> even if you didn't have stats, the estimate for a simple equality
> constraint oughtn't be 100% match.
>
> What do you get from SELECT * FROM pg_stats WHERE tablename = 'layertype'
> on both systems?
>
>             regards, tom lane
>
>
>

--
Christo Du Preez

Senior Software Engineer
Mecola IT
Mobile:     +27 [0]83 326 8087
Skype:     christodupreez
Website: http://www.locateandtrade.co.za