[postgis-users] long time for finding objects

[Paul Ramsey]

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 <paul.malm at saabgroup.com> 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: postgis-users-bounces at postgis.refractions.net [mailto:postgis-users-bounces at postgis.refractions.net] 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 <paul.malm at saabgroup.com> 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
>> postgis-users at postgis.refractions.net
>> http://postgis.refractions.net/mailman/listinfo/postgis-users
>>
>>
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>