pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedtest / live environment, major performance difference
20+ messages / 10 participants
[nested] [flat]
* test / live environment, major performance difference
@ 2007-06-11 15:10 Christo Du Preez <christo@mecola.com>
0 siblings, 2 replies; 20+ messages in thread
From: Christo Du Preez @ 2007-06-11 15:10 UTC (permalink / raw)
To: pgsql-performance
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
^ permalink raw reply [nested|flat] 20+ messages in thread
* Re: test / live environment, major performance difference
@ 2007-06-11 15:58 Ansgar -59cobalt- Wiechers <lists@planetcobalt.net>
parent: Christo Du Preez <christo@mecola.com>
1 sibling, 1 reply; 20+ messages in thread
From: Ansgar -59cobalt- Wiechers @ 2007-06-11 15:58 UTC (permalink / raw)
To: pgsql-performance
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
^ permalink raw reply [nested|flat] 20+ messages in thread
* Re: test / live environment, major performance difference
@ 2007-06-11 16:16 Dave Dutcher <dave@tridecap.com>
parent: Christo Du Preez <christo@mecola.com>
1 sibling, 1 reply; 20+ messages in thread
From: Dave Dutcher @ 2007-06-11 16:16 UTC (permalink / raw)
To: 'Christo Du Preez' <christo@mecola.com>; pgsql-performance
> -----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
^ permalink raw reply [nested|flat] 20+ messages in thread
* Re: test / live environment, major performance difference
@ 2007-06-11 18:26 Craig James <craig_james@emolecules.com>
parent: Ansgar -59cobalt- Wiechers <lists@planetcobalt.net>
0 siblings, 1 reply; 20+ messages in thread
From: Craig James @ 2007-06-11 18:26 UTC (permalink / raw)
To: ; +Cc: pgsql-performance
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
^ permalink raw reply [nested|flat] 20+ messages in thread
* Re: test / live environment, major performance difference
@ 2007-06-12 06:36 Christo Du Preez <christo@mecola.com>
parent: Craig James <craig_james@emolecules.com>
0 siblings, 0 replies; 20+ messages in thread
From: Christo Du Preez @ 2007-06-12 06:36 UTC (permalink / raw)
To: pgsql-performance
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
^ permalink raw reply [nested|flat] 20+ messages in thread
* Re: test / live environment, major performance difference
@ 2007-06-12 07:38 Christo Du Preez <christo@mecola.com>
parent: Dave Dutcher <dave@tridecap.com>
0 siblings, 1 reply; 20+ messages in thread
From: Christo Du Preez @ 2007-06-12 07:38 UTC (permalink / raw)
To: pgsql-performance
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
^ permalink raw reply [nested|flat] 20+ messages in thread
* Re: test / live environment, major performance difference
@ 2007-06-12 13:32 Christo Du Preez <christo@mecola.com>
0 siblings, 5 replies; 20+ messages in thread
From: Christo Du Preez @ 2007-06-12 13:32 UTC (permalink / raw)
To: pgsql-performance
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
^ permalink raw reply [nested|flat] 20+ messages in thread
* Re: test / live environment, major performance difference
@ 2007-06-12 13:45 Reid Thompson <Reid.Thompson@ateb.com>
parent: Christo Du Preez <christo@mecola.com>
4 siblings, 1 reply; 20+ messages in thread
From: Reid Thompson @ 2007-06-12 13:45 UTC (permalink / raw)
To: Christo Du Preez <christo@mecola.com>; +Cc: pgsql-performance
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
^ permalink raw reply [nested|flat] 20+ messages in thread
* Re: test / live environment, major performance difference
@ 2007-06-12 13:47 Michael Glaesemann <grzm@seespotcode.net>
parent: Christo Du Preez <christo@mecola.com>
4 siblings, 0 replies; 20+ messages in thread
From: Michael Glaesemann @ 2007-06-12 13:47 UTC (permalink / raw)
To: Christo Du Preez <christo@mecola.com>; +Cc: pgsql-performance
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
^ permalink raw reply [nested|flat] 20+ messages in thread
* Re: test / live environment, major performance difference
@ 2007-06-12 13:53 Gregory Stark <stark@enterprisedb.com>
parent: Christo Du Preez <christo@mecola.com>
4 siblings, 0 replies; 20+ messages in thread
From: Gregory Stark @ 2007-06-12 13:53 UTC (permalink / raw)
To: Christo Du Preez <christo@mecola.com>; +Cc: pgsql-performance
"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
^ permalink raw reply [nested|flat] 20+ messages in thread
* Re: test / live environment, major performance difference
@ 2007-06-12 14:11 Christo Du Preez <christo@mecola.com>
parent: Reid Thompson <Reid.Thompson@ateb.com>
0 siblings, 2 replies; 20+ messages in thread
From: Christo Du Preez @ 2007-06-12 14:11 UTC (permalink / raw)
To: Reid.Thompson@ateb.com; +Cc: pgsql-performance
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
^ permalink raw reply [nested|flat] 20+ messages in thread
* Re: test / live environment, major performance difference
@ 2007-06-12 14:24 Alvaro Herrera <alvherre@commandprompt.com>
parent: Christo Du Preez <christo@mecola.com>
1 sibling, 0 replies; 20+ messages in thread
From: Alvaro Herrera @ 2007-06-12 14:24 UTC (permalink / raw)
To: Christo Du Preez <christo@mecola.com>; +Cc: Reid.Thompson@ateb.com; pgsql-performance
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"
^ permalink raw reply [nested|flat] 20+ messages in thread
* Re: test / live environment, major performance difference
@ 2007-06-12 14:28 Dave Dutcher <dave@tridecap.com>
parent: Christo Du Preez <christo@mecola.com>
0 siblings, 0 replies; 20+ messages in thread
From: Dave Dutcher @ 2007-06-12 14:28 UTC (permalink / raw)
To: 'Christo Du Preez' <christo@mecola.com>; pgsql-performance
> 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
^ permalink raw reply [nested|flat] 20+ messages in thread
* Re: test / live environment, major performance difference
@ 2007-06-12 14:30 Steinar H. Gunderson <sgunderson@bigfoot.com>
parent: Christo Du Preez <christo@mecola.com>
4 siblings, 0 replies; 20+ messages in thread
From: Steinar H. Gunderson @ 2007-06-12 14:30 UTC (permalink / raw)
To: Christo Du Preez <christo@mecola.com>; +Cc: pgsql-performance
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/
^ permalink raw reply [nested|flat] 20+ messages in thread
* Re: test / live environment, major performance difference
@ 2007-06-12 14:59 Steinar H. Gunderson <sgunderson@bigfoot.com>
parent: Christo Du Preez <christo@mecola.com>
1 sibling, 0 replies; 20+ messages in thread
From: Steinar H. Gunderson @ 2007-06-12 14:59 UTC (permalink / raw)
To: Christo Du Preez <christo@mecola.com>; +Cc: Reid.Thompson@ateb.com; pgsql-performance
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/
^ permalink raw reply [nested|flat] 20+ messages in thread
* Re: test / live environment, major performance difference
@ 2007-06-12 16:09 Tom Lane <tgl@sss.pgh.pa.us>
parent: Christo Du Preez <christo@mecola.com>
4 siblings, 1 reply; 20+ messages in thread
From: Tom Lane @ 2007-06-12 16:09 UTC (permalink / raw)
To: Christo Du Preez <christo@mecola.com>; +Cc: pgsql-performance
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
^ permalink raw reply [nested|flat] 20+ messages in thread
* Re: test / live environment, major performance difference
@ 2007-06-12 16:59 Christo Du Preez <christo@mecola.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 20+ messages in thread
From: Christo Du Preez @ 2007-06-12 16:59 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-performance
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
^ permalink raw reply [nested|flat] 20+ messages in thread
* Re: test / live environment, major performance difference
@ 2007-06-12 17:37 Tom Lane <tgl@sss.pgh.pa.us>
parent: Christo Du Preez <christo@mecola.com>
0 siblings, 1 reply; 20+ messages in thread
From: Tom Lane @ 2007-06-12 17:37 UTC (permalink / raw)
To: Christo Du Preez <christo@mecola.com>; +Cc: pgsql-performance
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
^ permalink raw reply [nested|flat] 20+ messages in thread
* Re: test / live environment, major performance difference
@ 2007-06-12 17:53 Christo Du Preez <christo@mecola.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 20+ messages in thread
From: Christo Du Preez @ 2007-06-12 17:53 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: 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
^ permalink raw reply [nested|flat] 20+ messages in thread
* Re: test / live environment, major performance difference
@ 2007-06-12 18:42 Tom Lane <tgl@sss.pgh.pa.us>
parent: Christo Du Preez <christo@mecola.com>
0 siblings, 0 replies; 20+ messages in thread
From: Tom Lane @ 2007-06-12 18:42 UTC (permalink / raw)
To: Christo Du Preez <christo@mecola.com>; +Cc: pgsql-performance
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
^ permalink raw reply [nested|flat] 20+ messages in thread
end of thread, other threads:[~2007-06-12 18:42 UTC | newest]
Thread overview: 20+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-06-11 15:10 test / live environment, major performance difference Christo Du Preez <christo@mecola.com>
2007-06-11 15:58 ` Ansgar -59cobalt- Wiechers <lists@planetcobalt.net>
2007-06-11 18:26 ` Craig James <craig_james@emolecules.com>
2007-06-12 06:36 ` Christo Du Preez <christo@mecola.com>
2007-06-11 16:16 ` Dave Dutcher <dave@tridecap.com>
2007-06-12 07:38 ` Christo Du Preez <christo@mecola.com>
2007-06-12 14:28 ` Dave Dutcher <dave@tridecap.com>
2007-06-12 13:32 Re: test / live environment, major performance difference Christo Du Preez <christo@mecola.com>
2007-06-12 13:45 ` Reid Thompson <Reid.Thompson@ateb.com>
2007-06-12 14:11 ` Christo Du Preez <christo@mecola.com>
2007-06-12 14:24 ` Alvaro Herrera <alvherre@commandprompt.com>
2007-06-12 14:59 ` Steinar H. Gunderson <sgunderson@bigfoot.com>
2007-06-12 13:47 ` Michael Glaesemann <grzm@seespotcode.net>
2007-06-12 13:53 ` Gregory Stark <stark@enterprisedb.com>
2007-06-12 14:30 ` Steinar H. Gunderson <sgunderson@bigfoot.com>
2007-06-12 16:09 ` Tom Lane <tgl@sss.pgh.pa.us>
2007-06-12 16:59 ` Christo Du Preez <christo@mecola.com>
2007-06-12 17:37 ` Tom Lane <tgl@sss.pgh.pa.us>
2007-06-12 17:53 ` Christo Du Preez <christo@mecola.com>
2007-06-12 18:42 ` Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox