Thread: test / live environment, major performance difference
Hi All,
I really hope someone can shed some light on my problem. I'm not sure if
this is a posgres or potgis issue.
Anyway, we have 2 development laptops and one live server, somehow I
managed to get the same query to perform very well om my laptop, but on
both the server and the other laptop it's really performing bad.
All three environments are running the same versions of everything, the
two laptops are identical and the server is a monster compared to the
laptops.
I have narrowed down the problem (I think) and it's the query planner
using different plans and I haven't got a clue why. Can anyone please
shed some light on this?
EXPLAIN ANALYZE
SELECT l.*
FROM layer l, theme t, visiblelayer v, layertype lt, style s
WHERE l.the_geom && geomfromtext('POLYGON((-83.0 -90.0, -83.0 90.0, 97.0
90.0, 97.0 -90.0, -83.0 -90.0))')
AND t.name = 'default'
AND v.themeid = t.id
AND v.zoomlevel = 1
AND v.enabled
AND l.layertypeid = v.layertypeid
AND lt.id = l.layertypeid
AND s.id = v.styleid
ORDER BY lt.zorder ASC
----------------------------------
Sort (cost=181399.77..182144.30 rows=297812 width=370) (actual
time=1384.976..1385.072 rows=180 loops=1)
Sort Key: lt.zorder
-> Hash Join (cost=31.51..52528.64 rows=297812 width=370) (actual
time=398.656..1384.574 rows=180 loops=1)
Hash Cond: (l.layertypeid = v.layertypeid)
-> Seq Scan on layer l (cost=0.00..43323.41 rows=550720
width=366) (actual time=0.016..1089.049 rows=540490 loops=1)
Filter: (the_geom &&
'010300000001000000050000000000000000C054C000000000008056C00000000000C054C0000000000080564000000000004058400000000000805640000000000040584000000000008056C00000000000C054C000000000008056C0'::geometry)
-> Hash (cost=31.42..31.42 rows=7 width=12) (actual
time=1.041..1.041 rows=3 loops=1)
-> Hash Join (cost=3.90..31.42 rows=7 width=12) (actual
time=0.107..1.036 rows=3 loops=1)
Hash Cond: (v.styleid = s.id)
-> Nested Loop (cost=2.74..30.17 rows=7 width=16)
(actual time=0.080..1.002 rows=3 loops=1)
Join Filter: (v.themeid = t.id)
-> Seq Scan on theme t (cost=0.00..1.01
rows=1 width=4) (actual time=0.004..0.005 rows=1 loops=1)
Filter: (name = 'default'::text)
-> Hash Join (cost=2.74..29.07 rows=7
width=20) (actual time=0.071..0.988 rows=3 loops=1)
Hash Cond: (lt.id = v.layertypeid)
-> Seq Scan on layertype lt
(cost=0.00..18.71 rows=671 width=8) (actual time=0.007..0.473 rows=671
loops=1)
-> Hash (cost=2.65..2.65 rows=7
width=12) (actual time=0.053..0.053 rows=3 loops=1)
-> Seq Scan on visiblelayer v
(cost=0.00..2.65 rows=7 width=12) (actual time=0.022..0.047 rows=3 loops=1)
Filter: ((zoomlevel = 1)
AND enabled)
-> Hash (cost=1.07..1.07 rows=7 width=4) (actual
time=0.020..0.020 rows=7 loops=1)
-> Seq Scan on style s (cost=0.00..1.07
rows=7 width=4) (actual time=0.005..0.012 rows=7 loops=1)
Total runtime: 1385.313 ms
----------------------------------
Sort (cost=37993.10..37994.11 rows=403 width=266) (actual
time=32.053..32.451 rows=180 loops=1)
Sort Key: lt.zorder
-> Nested Loop (cost=0.00..37975.66 rows=403 width=266) (actual
time=0.130..31.254 rows=180 loops=1)
-> Nested Loop (cost=0.00..30.28 rows=1 width=12) (actual
time=0.105..0.873 rows=3 loops=1)
-> Nested Loop (cost=0.00..23.14 rows=1 width=4)
(actual time=0.086..0.794 rows=3 loops=1)
-> Nested Loop (cost=0.00..11.14 rows=2 width=8)
(actual time=0.067..0.718 rows=3 loops=1)
Join Filter: (s.id = v.styleid)
-> Seq Scan on style s (cost=0.00..2.02
rows=2 width=4) (actual time=0.018..0.048 rows=7 loops=1)
-> Seq Scan on visiblelayer v
(cost=0.00..4.47 rows=7 width=12) (actual time=0.031..0.079 rows=3 loops=7)
Filter: ((zoomlevel = 1) AND enabled)
-> Index Scan using theme_id_pkey on theme t
(cost=0.00..5.98 rows=1 width=4) (actual time=0.009..0.012 rows=1 loops=3)
Index Cond: (v.themeid = t.id)
Filter: (name = 'default'::text)
-> Index Scan using layertype_id_pkey on layertype lt
(cost=0.00..7.12 rows=1 width=8) (actual time=0.010..0.014 rows=1 loops=3)
Index Cond: (lt.id = v.layertypeid)
-> Index Scan using fki_layer_layertypeid on layer l
(cost=0.00..36843.10 rows=88183 width=262) (actual time=0.031..9.825
rows=60 loops=3)
Index Cond: (l.layertypeid = v.layertypeid)
Filter: (the_geom &&
'010300000001000000050000000000000000C054C000000000008056C00000000000C054C0000000000080564000000000004058400000000000805640000000000040584000000000008056C00000000000C054C000000000008056C0'::geometry)
Total runtime: 33.107 ms
----------------------------------
Thanx in advance.
Christo Du Preez
On 2007-06-11 Christo Du Preez wrote: > I really hope someone can shed some light on my problem. I'm not sure > if this is a posgres or potgis issue. > > Anyway, we have 2 development laptops and one live server, somehow I > managed to get the same query to perform very well om my laptop, but > on both the server and the other laptop it's really performing bad. You write that you have 3 systems, but provided only two EXPLAIN ANALYZE results. I will assume that the latter is from your laptop while the former is from one of the badly performing systems. > All three environments are running the same versions of everything, > the two laptops are identical and the server is a monster compared to > the laptops. Please provide information what exactly those "same versions of everything" are. What's the PostgreSQL configuration on each system? Do all three systems have the same configuration? Information on the hardware wouldn't hurt either. [...] > Sort (cost=181399.77..182144.30 rows=297812 width=370) (actual > time=1384.976..1385.072 rows=180 loops=1) [...] > Sort (cost=37993.10..37994.11 rows=403 width=266) (actual > time=32.053..32.451 rows=180 loops=1) The row estimate of the former plan is way off (297812 estimated <-> 180 actual). Did you analyze the table recently? Maybe you need to increase the statistics target. Regards Ansgar Wiechers -- "The Mac OS X kernel should never panic because, when it does, it seriously inconveniences the user." --http://developer.apple.com/technotes/tn2004/tn2118.html
> -----Original Message----- > From: Christo Du Preez > Sent: Monday, June 11, 2007 10:10 AM > > I have narrowed down the problem (I think) and it's the query > planner using different plans and I haven't got a clue why. > Can anyone please shed some light on this? Different plans can be caused by several different things like different server versions, different planner settings in the config file, different schemas, or different statistics. You say the server versions are the same, so that's not it. Is the schema the same? One isn't missing indexes that the other has? Do they both have the same data, or at least very close to the same data? Have you run analyze on both of them to update their statistics? Do they have the same planner settings in the config file? I would check that stuff out and see if it helps. Dave
On 2007-06-11 Christo Du Preez wrote: > I really hope someone can shed some light on my problem. I'm not sure > if this is a posgres or potgis issue. > > Anyway, we have 2 development laptops and one live server, somehow I > managed to get the same query to perform very well om my laptop, but > on both the server and the other laptop it's really performing bad. One simple possibility that bit me in the past: If you do pg_dump/pg_restore to create a copy of the database, you have toANALYZE the newly-restored database. I mistakenly assumed that pg_restore would do this, but you have to run ANALYZE explicitelyafter a restore. Craig
I wonder if my dump/restore routine isn't causing this issue. Seeing that I do the db development on my laptop (the fast one) and then restores it on the other two machines. I have confirmed if all the indexes are present after a restore. This is the routine: /usr/local/pgsql/bin/pg_dump -t layer mapdb | gzip > layer.gz rsync --progress --rsh=ssh layer.gz root@???.???.???.???:/home/postgres/layer.gz -- /usr/local/pgsql/bin/pg_dump -t visiblelayer mapdb | gzip > visiblelayer.gz rsync --progress --rsh=ssh visiblelayer.gz root@???.???.???.???:/home/postgres/visiblelayer.gz -- /usr/local/pgsql/bin/pg_dump -t style mapdb | gzip > style.gz rsync --progress --rsh=ssh style.gz root@???.???.???.???:/home/postgres/style.gz -- /usr/local/pgsql/bin/pg_dump -t layertype mapdb | gzip > layertype.gz rsync --progress --rsh=ssh layertype.gz root@???.???.???.???:/home/postgres/layertype.gz -- DROP TABLE visiblelayer; DROP TABLE style; DROP TABLE layer; DROP TABLE layertype; gunzip -c layertype.gz | /usr/local/pgsql/bin/psql mapdb gunzip -c style.gz | /usr/local/pgsql/bin/psql mapdb gunzip -c visiblelayer.gz | /usr/local/pgsql/bin/psql mapdb gunzip -c layer.gz | /usr/local/pgsql/bin/psql mapdb /usr/local/pgsql/bin/vacuumdb -d mapdb -z -v Craig James wrote: > > On 2007-06-11 Christo Du Preez wrote: >> I really hope someone can shed some light on my problem. I'm not sure >> if this is a posgres or potgis issue. >> >> Anyway, we have 2 development laptops and one live server, somehow I >> managed to get the same query to perform very well om my laptop, but >> on both the server and the other laptop it's really performing bad. > > One simple possibility that bit me in the past: If you do > pg_dump/pg_restore to create a copy of the database, you have to > ANALYZE the newly-restored database. I mistakenly assumed that > pg_restore would do this, but you have to run ANALYZE explicitely > after a restore. > > Craig > > > ---------------------------(end of broadcast)--------------------------- > TIP 1: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo@postgresql.org so that your > message can get through to the mailing list cleanly > > -- Christo Du Preez Senior Software Engineer Mecola IT Mobile: +27 [0]83 326 8087 Skype: christodupreez Website: http://www.locateandtrade.co.za
Where do I set the planner settings or are you reffering to settings in postgres.conf that may affect the planner? The one badly performing laptop is the same as mine (the fast one) and the server is much more powerful. Laptops: Intel Centrino Duo T2600 @ 2.16GHz, 1.98 GB RAM Server: 2 xIntel Pentium D CPU 3.00GHz, 4 GB RAM All three systems are running Suse 10.2, with the same PosgreSQL, same configs, same databases. As far as I know, same everything. PostgreSQL 8.2.3 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 4.1.2 20061115 (prerelease) (SUSE Linux) POSTGIS="1.2.1" GEOS="3.0.0rc4-CAPI-1.3.3" PROJ="Rel. 4.5.0, 22 Oct 2006" USE_STATS Thanx for all the advice Dave Dutcher wrote: >> -----Original Message----- >> From: Christo Du Preez >> Sent: Monday, June 11, 2007 10:10 AM >> >> I have narrowed down the problem (I think) and it's the query >> planner using different plans and I haven't got a clue why. >> Can anyone please shed some light on this? >> > > Different plans can be caused by several different things like different > server versions, different planner settings in the config file, different > schemas, or different statistics. You say the server versions are the same, > so that's not it. Is the schema the same? One isn't missing indexes that > the other has? Do they both have the same data, or at least very close to > the same data? Have you run analyze on both of them to update their > statistics? Do they have the same planner settings in the config file? I > would check that stuff out and see if it helps. > > Dave > > > > -- Christo Du Preez Senior Software Engineer Mecola IT Mobile: +27 [0]83 326 8087 Skype: christodupreez Website: http://www.locateandtrade.co.za
Good day, I have noticed that my server never uses indexing. No matter what I do. As an example I took a table with about 650 rows, having a parentid field with an index on parentid. EXPLAIN ANALYZE SELECT * FROM layertype where parentid = 300; On my laptop the explain analyze looks like this: "Index Scan using fki_layertype_parentid on layertype (cost=0.00..8.27 rows=1 width=109)" " Index Cond: (parentid = 300)" and on the problem server: "Seq Scan on layertype (cost=0.00..20.39 rows=655 width=110)" " Filter: (parentid = 300)" ......... I have dropped the index, recreated it, vacuumed the table, just about everything I could think of, And there is just no way I can get the query planner to use the index. PostgreSQL 8.2.3 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 4.1.2 20061115 (prerelease) (SUSE Linux) POSTGIS="1.2.1" GEOS="3.0.0rc4-CAPI-1.3.3" PROJ="Rel. 4.5.0, 22 Oct 2006" USE_STATS
try it with a table with 650K rows... On Tue, 2007-06-12 at 15:32 +0200, Christo Du Preez wrote: > Good day, > > I have noticed that my server never uses indexing. No matter what I do. > > As an example I took a table with about 650 rows, having a parentid > field with an index on parentid. > > EXPLAIN ANALYZE > SELECT * > FROM layertype > where parentid = 300; > > On my laptop the explain analyze looks like this: > > "Index Scan using fki_layertype_parentid on layertype (cost=0.00..8.27 > rows=1 width=109)" > " Index Cond: (parentid = 300)" > > and on the problem server: > > "Seq Scan on layertype (cost=0.00..20.39 rows=655 width=110)" > " Filter: (parentid = 300)" > > ......... > > I have dropped the index, recreated it, vacuumed the table, just about > everything I could think of, And there is just no way I can get the > query planner to use the index. > > PostgreSQL 8.2.3 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 4.1.2 > 20061115 (prerelease) (SUSE Linux) > POSTGIS="1.2.1" GEOS="3.0.0rc4-CAPI-1.3.3" PROJ="Rel. 4.5.0, 22 Oct > 2006" USE_STATS > > > ---------------------------(end of broadcast)--------------------------- > TIP 3: Have you checked our extensive FAQ? > > http://www.postgresql.org/docs/faq
On Jun 12, 2007, at 8:32 , Christo Du Preez wrote: > I have noticed that my server never uses indexing. No matter what I > do. > > As an example I took a table with about 650 rows, having a parentid > field with an index on parentid. > > EXPLAIN ANALYZE > SELECT * > FROM layertype > where parentid = 300; The planner weighs the cost of the different access methods and choses the one that it believes is lowest in cost. An index scan is not always faster than a sequential scan. With so few rows, it's probably faster for the server to read the whole table rather than reading the index and looking up the corresponding row. If you want to test this, you can set enable_seqscan to false and try running your query again. http://www.postgresql.org/docs/8.2/interactive/runtime-config- query.html#RUNTIME-CONFIG-QUERY-ENABLE Michael Glaesemann grzm seespotcode net
"Christo Du Preez" <christo@mecola.com> writes: > On my laptop the explain analyze looks like this: > > "Index Scan using fki_layertype_parentid on layertype (cost=0.00..8.27 > rows=1 width=109)" > " Index Cond: (parentid = 300)" That's not "explain analyze", that's just plain "explain". -- Gregory Stark EnterpriseDB http://www.enterprisedb.com
The actual table I noticed the problem has a million rows and it still doesn't use indexing Reid Thompson wrote: > try it with a table with 650K rows... > > On Tue, 2007-06-12 at 15:32 +0200, Christo Du Preez wrote: > >> Good day, >> >> I have noticed that my server never uses indexing. No matter what I do. >> >> As an example I took a table with about 650 rows, having a parentid >> field with an index on parentid. >> >> EXPLAIN ANALYZE >> SELECT * >> FROM layertype >> where parentid = 300; >> >> On my laptop the explain analyze looks like this: >> >> "Index Scan using fki_layertype_parentid on layertype (cost=0.00..8.27 >> rows=1 width=109)" >> " Index Cond: (parentid = 300)" >> >> and on the problem server: >> >> "Seq Scan on layertype (cost=0.00..20.39 rows=655 width=110)" >> " Filter: (parentid = 300)" >> >> ......... >> >> I have dropped the index, recreated it, vacuumed the table, just about >> everything I could think of, And there is just no way I can get the >> query planner to use the index. >> >> PostgreSQL 8.2.3 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 4.1.2 >> 20061115 (prerelease) (SUSE Linux) >> POSTGIS="1.2.1" GEOS="3.0.0rc4-CAPI-1.3.3" PROJ="Rel. 4.5.0, 22 Oct >> 2006" USE_STATS >> >> >> ---------------------------(end of broadcast)--------------------------- >> TIP 3: Have you checked our extensive FAQ? >> >> http://www.postgresql.org/docs/faq >> > > > -- Christo Du Preez Senior Software Engineer Mecola IT Mobile: +27 [0]83 326 8087 Skype: christodupreez Website: http://www.locateandtrade.co.za
Christo Du Preez wrote: > The actual table I noticed the problem has a million rows and it still > doesn't use indexing So ANALYZE it. -- Alvaro Herrera Developer, http://www.PostgreSQL.org/ "Amanece. (Ignacio Reyes) El Cerro San Cristóbal me mira, cínicamente, con ojos de virgen"
> From: Christo Du Preez > Sent: Tuesday, June 12, 2007 2:38 AM > > Where do I set the planner settings or are you reffering to > settings in postgres.conf that may affect the planner? > Yes I'm reffering to settings in postgres.conf. I'm wondering if enable_indexscan or something got turned off on the server for some reason. Here is a description of those settings: http://www.postgresql.org/docs/8.2/interactive/runtime-config-query.html So when you move data from the laptop to the server, I see that your script correctly runs an analyze after the load, so have you run analyze on the fast laptop lately? Hopefully running analyze wouldn't make the planner choose a worse plan on the laptop, but if we are trying to get things consistant between the laptop and server, that is something I would try. If the consistancy problem really is a problem of the planner not using index scans on the server, then if you can, please post the table definition for the table with a million rows and an EXPLAIN ANALYZE of a query which selects a few rows from the table. Dave
On Tue, Jun 12, 2007 at 03:32:40PM +0200, Christo Du Preez wrote: > As an example I took a table with about 650 rows, having a parentid > field with an index on parentid. Try a bigger table. Using an index for only 650 rows is almost always suboptimal, so it's no wonder the planner doesn't use the index. /* Steinar */ -- Homepage: http://www.sesse.net/
On Tue, Jun 12, 2007 at 04:11:33PM +0200, Christo Du Preez wrote: > The actual table I noticed the problem has a million rows and it still > doesn't use indexing Then please post an EXPLAIN ANALYZE of the query that is slow, along with the table definition and indexes. /* Steinar */ -- Homepage: http://www.sesse.net/
Christo Du Preez <christo@mecola.com> writes:
> On my laptop the explain analyze looks like this:
> "Index Scan using fki_layertype_parentid on layertype (cost=0.00..8.27
> rows=1 width=109)"
> " Index Cond: (parentid = 300)"
OK ...
> and on the problem server:
> "Seq Scan on layertype (cost=0.00..20.39 rows=655 width=110)"
> " Filter: (parentid = 300)"
The server thinks that every row of the table matches the WHERE clause.
That being the case, it's making the right choice to use a seqscan.
The question is why is the rows estimate so far off? Have you ANALYZEd
the table lately?
regards, tom lane
Yes, I have just about tried every combination of vacuum on the database. Just to make 100% sure. Tom Lane wrote: > Christo Du Preez <christo@mecola.com> writes: > >> On my laptop the explain analyze looks like this: >> > > >> "Index Scan using fki_layertype_parentid on layertype (cost=0.00..8.27 >> rows=1 width=109)" >> " Index Cond: (parentid = 300)" >> > > OK ... > > >> and on the problem server: >> > > >> "Seq Scan on layertype (cost=0.00..20.39 rows=655 width=110)" >> " Filter: (parentid = 300)" >> > > The server thinks that every row of the table matches the WHERE clause. > That being the case, it's making the right choice to use a seqscan. > The question is why is the rows estimate so far off? Have you ANALYZEd > the table lately? > > 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
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
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
Christo Du Preez <christo@mecola.com> writes:
> Fast:
> "public";"layertype";"parentid";0.98797;4;2;"{4,1}";"{0.00902256,0.00300752}";"";-0.142857
> Slow:
> "public";"layertype";"parentid";0.00745157;4;7;"{300}";"{0.976155}";"{1,1,4,5,8,12}";0.92262
Well, those statistics are almost completely different, and what the
slow one says is that parentid = 300 accounts for 97% of the table.
So that's why you get different plans. If that is not reflective of
reality, then you have not ANALYZEd the table lately.
Maybe it's a pilot-error problem, like not doing the ANALYZE as a user
with sufficient privileges? IIRC you have to be table owner, database
owner, or superuser to ANALYZE.
regards, tom lane