Don't we have our explanation? You are querying with PgAdmin. When querying this:
select bytea from map; it takes a long time. When querying this select length(bytea) from map; it takes a short time. Ergo, the fetch is fast, but the act of PgAdmin rendering the (large, text-wise) bytea result into text in your UI is long. P. On Wed, Apr 29, 2009 at 8:19 AM, Malm Paul <[email protected]> wrote: > Paul, > First of all:, I'm not to good at databases. > > Terminal or client? I suppose Client, I've installed the db on my PC and > selects through PGAdmin. > > Yes, it takes about 1 sec the second time. > > Yes, "SELECT length(binary_field) FROM map" takes about 16 ms. > > Do you know anything that could speed up the time from query to resultset? > > Thanks, > Paul > > > -----Original Message----- > From: [email protected] > [mailto:[email protected]] On Behalf Of Paul > Ramsey > Sent: den 29 april 2009 16:51 > To: PostGIS Users Discussion > Subject: Re: [postgis-users] long time for finding objects > > Well, the second or third time you run your slow query, I'd hope it would be > fast, if only because everything is getting pulled into cache by then. In > general, you are doing 81 large object operations, since your binaries are > well over the page size. > > 3 seconds does sound slow for pulling 81 of anything, although if you're > testing with a terminal or a client, a good deal of the time could be writing > the output into the UI, rather than the actual fetch. > Wrapping the large objects in functions that summarize them will give you a > better feel for the true speed (e.g., instead of pulling geom, pull > st_area(geom), instead of pulling bytea, pull length(bytea). > > P > > On Wed, Apr 29, 2009 at 7:32 AM, Malm Paul <[email protected]> wrote: >> Hi I find it slow to select objects in the PostgreSQL/PostGIS db I'm >> using windows XP, postgreSQL 8.3.7 >> >> I have a table "map" with the columns: >> mapid: small int >> scale: small int >> rectangle: geometry >> binary_field: bytea >> >> The database concist of 81 "map" rows, where the binary has a avarage >> size of 80kb. >> >> "SELECT * FROM map" takes about 3 seconds, while "SELECT mapid, scale >> from map" takes about 14 milliseconds >> >> Total size = 80 x 81k = 6,3 mb ( I don' t know, perhaps 3 sec isn' t >> slow ) >> >> Is there a way to speed this up? >> I've tried to increase shared_buffers, and effective_cash_size. >> >> Kind regards, >> Paul >> >> _______________________________________________ >> 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 > _______________________________________________ > 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
