[fdo-users] Another FDO PostGIS Selection Problem

[ary sucaya]

Well, I turn on postgres logging. I found there are 6 query during deselect process:

SELECT "public"."fotoudara".batas,"public"."fotoudara".lembar,"public"."fotoudara".tahun,"public"."fotoudara".akurasi,"public"."fotoudara".proyeksi,"public"."fotoudara".format,"public"."fotoudara".sumber,"public"."fotoudara".pemilik,"public"."fotoudara".kontak,"public"."fotoudara".catatan FROM "public"."fotoudara" WHERE  (  ( lembar LIKE '50.1%' )  AND  ( batas && GeomFromWKB(decode('01030000000100000005000000AB3AB267A57D5C404EBC847AC10226C0CFA7527EA67D5C404DBC847AC10226C0CFA7527EA67D5C407225882FCA0226C0AB3AB267A57D5C407225882FCA0226C0AB3AB267A57D5C404EBC847AC10226C0', 'hex'),4326) AND Intersects(batas,GeomFromWKB(decode('01030000000100000005000000AB3AB267A57D5C404EBC847AC10226C0CFA7527EA67D5C404DBC847AC10226C0CFA7527EA67D5C407225882FCA0226C0AB3AB267A57D5C407225882FCA0226C0AB3AB267A57D5C404EBC847AC10226C0', 'hex'),4326)) )  );
SELECT "public"."fotoudara".batas,"public"."fotoudara".lembar,"public"."fotoudara".tahun,"public"."fotoudara".akurasi,"public"."fotoudara".proyeksi,"public"."fotoudara".format,"public"."fotoudara".sumber,"public"."fotoudara".pemilik,"public"."fotoudara".kontak,"public"."fotoudara".catatan FROM "public"."fotoudara"
SELECT "public"."kecamatan".batas,"public"."kecamatan".gid,"public"."kecamatan".kode,"public"."kecamatan".nama,"public"."kecamatan".hm,"public"."kecamatan".hgb,"public"."kecamatan".hp,"public"."kecamatan".hw,"public"."kecamatan".fgid FROM "public"."kecamatan" WHERE  ( batas && GeomFromWKB(decode('01030000000100000005000000AB3AB267A57D5C404EBC847AC10226C0CFA7527EA67D5C404DBC847AC10226C0CFA7527EA67D5C407225882FCA0226C0AB3AB267A57D5C407225882FCA0226C0AB3AB267A57D5C404EBC847AC10226C0', 'hex'),4326) AND Intersects(batas,GeomFromWKB(decode('01030000000100000005000000AB3AB267A57D5C404EBC847AC10226C0CFA7527EA67D5C404DBC847AC10226C0CFA7527EA67D5C407225882FCA0226C0AB3AB267A57D5C407225882FCA0226C0AB3AB267A57D5C404EBC847AC10226C0', 'hex'),4326)) );
SELECT "public"."kecamatan".batas,"public"."kecamatan".gid,"public"."kecamatan".kode,"public"."kecamatan".nama,"public"."kecamatan".hm,"public"."kecamatan".hgb,"public"."kecamatan".hp,"public"."kecamatan".hw,"public"."kecamatan".fgid FROM "public"."kecamatan";
SELECT "public"."kecamatan".batas,"public"."kecamatan".gid,"public"."kecamatan".kode,"public"."kecamatan".nama,"public"."kecamatan".hm,"public"."kecamatan".hgb,"public"."kecamatan".hp,"public"."kecamatan".hw,"public"."kecamatan".fgid FROM "public"."kecamatan" WHERE  ( batas && GeomFromWKB(decode('01030000000100000005000000D1C4E395577D5C4033EF6181D80626C09BF8A78A2E7E5C4033EF6181D80626C09BF8A78A2E7E5C408584CFCF310226C0D1C4E395577D5C408584CFCF310226C0D1C4E395577D5C4033EF6181D80626C0', 'hex'),4326) );
SELECT "public"."fotoudara".batas,"public"."fotoudara".lembar,"public"."fotoudara".tahun,"public"."fotoudara".akurasi,"public"."fotoudara".proyeksi,"public"."fotoudara".format,"public"."fotoudara".sumber,"public"."fotoudara".pemilik,"public"."fotoudara".kontak,"public"."fotoudara".catatan FROM "public"."fotoudara" WHERE  (  ( lembar LIKE '50.1%' )  AND  ( batas && GeomFromWKB(decode('01030000000100000005000000D1C4E395577D5C4033EF6181D80626C09BF8A78A2E7E5C4033EF6181D80626C09BF8A78A2E7E5C408584CFCF310226C0D1C4E395577D5C408584CFCF310226C0D1C4E395577D5C4033EF6181D80626C0', 'hex'),4326) )  )


The reds sql above takes time, which are 609 ms (2803 rows) and 40704 ms (3994 rows) respectively. It looks like that sql make query for whole table. I couldn't understand why it makes query for whole tables. The other query limited by GeomFrom WKB and not takes time. By the time i did deselect, my map windows consists of two feature which are returned by 6rd sql above. 
Now my further question, if my database growing larger and larger, it will takes longer. Could you explain me?
BTW, i deselect feature by clicking on empty area, not by deselect toolbar


Regards,

Ary Sucaya
----- Original Message ----
From: Jason Birch <Jason.Birch at nanaimo.ca>
To: FDO Users Mail List <fdo-users at lists.osgeo.org>
Sent: Monday, March 17, 2008 9:03:43 PM
Subject: RE: [fdo-users] Another FDO PostGIS Selection Problem

I think that Mateusz is suggesting the following:

- Turn on statement logging for PostgreSQL:

http://www.postgresql.org/docs/current/static/runtime-config-logging.htm
l

- Perform the operation that is failing

- Retrieve the SQL text of the queries from the PostgreSQL logs

- Run the queries through the postgresql command line until you find the
one with poor performance

- Run this through EXPLAIN to find out why it's so slow:
  http://www.postgresql.org/docs/8.3/static/sql-explain.html 

Often, when this kind of performance problem happens it is because the
initial indexed select query fails, and MapGuide falls back to an
unconstrained query.

How are you clearing selection?  Clicking on a place with no known
features, or using a "clear select" button?

Jason

_______________________________________________
fdo-users mailing list
fdo-users at lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/fdo-users

__________________________________________________
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around 
http://mail.yahoo.com 
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.osgeo.org/pipermail/fdo-users/attachments/20080317/7b239bf0/attachment-0001.html