Pedro -- Is there an index on customer_id ? And have you analyzed the table after loading it to update the statistics ?
HTH Greg Williamson ----- Original Message ---- From: Pedro Doria Meunier <[email protected]> To: PostGIS Users Discussion <[email protected]> Sent: Friday, June 12, 2009 1:54:11 AM Subject: Re: [postgis-users] Centroid out a list of points -----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 Thank you Strk. The actual query is as thus: select astext(st_centroid(st_collect(coordinates))) from units where customer_id=someid So there's a 'where' clause in it :] EXPLAIN ANALYZE tells me: "Aggregate (cost=49790.11..49790.13 rows=1 width=25) (actual time=524.380..524.381 rows=1 loops=1)" " -> Seq Scan on units (cost=0.00..49790.09 rows=9 width=25) (actual time=445.782..524.231 rows=4 loops=1)" " Filter: (customer_id = 20)" "Total runtime: 524.451 ms" The runtime is a bit stiff, don't you agree? ;-) BR, Pedro Doria Meunier GSM: +351 96 17 20 188 Skype: pdoriam strk wrote: > On Fri, Jun 12, 2009 at 10:34:21AM +0100, Pedro Doria Meunier > wrote: >> -----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 >> >> Thank you Nicklas for your advice! That actually accomplishes it >> with a notable exception: >> >> Altough the table has a gist index on the geom column the query >> is always executed in Seq Scan mode... >> >> I ask the gurus which fx is causing this, st_centroid or >> st_collect ? > > It's the WHERE clause driving access strategy. If you're giving no > conditions of course sequential scan is faster. Only reason to use > the gist geometry index is if you use the && operator in the WHERE > clause, and the analizer finds it to be enough selective to prefere > index vs. sequential scan. > > --strk; > > Free GIS & Flash consultant/developer () ASCII Ribbon > Campaign http://foo.keybit.net/~strk/services.html /\ Keep it > simple! _______________________________________________ > postgis-users mailing list [email protected] > http://postgis.refractions.net/mailman/listinfo/postgis-users > -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.4.7 (GNU/Linux) Comment: Using GnuPG with Fedora - http://enigmail.mozdev.org iD8DBQFKMiW62FH5GXCfxAsRAoCeAJ9hzJ3AUN5WnpdrDJ2Vt4BSmuS7vACgs9I2 Z3Spv30GloYiJvlDyL+4caI= =PLXK -----END PGP SIGNATURE----- _______________________________________________ postgis-users mailing list [email protected] http://postgis.refractions.net/mailman/listinfo/postgis-users _______________________________________________ postgis-users mailing list [email protected] http://postgis.refractions.net/mailman/listinfo/postgis-users
