[Qgis-developer] Re: Wroclaw taking several minutes to load a single postgis polygon

[Tim Keitt]

Yes. Loading one postgis table causes a scan of data related to all
other tables in the database. The query below appears in the
postgresql log, but it is not the table loaded into qgis. The query
took more than 6 minutes.

THK

2011-09-12 21:09:34 CDT LOG:  duration: 394152.953 ms  statement:
select distinct case when geometrytype("the_geom") IN
('POINT','MULTIPOINT') THEN 'POINT' when geometrytype("the_geom") IN
('LINESTRING','MULTILINESTRING') THEN 'LINESTRING' when
geometrytype("the_geom") IN ('POLYGON','MULTIPOLYGON') THEN 'POLYGON'
end from "public"."dual_cmtc_geom"



On Mon, Sep 12, 2011 at 9:00 PM, Tim Keitt <tkeitt at gmail.com> wrote:
> I see that the output is not so helpful below because the log is not serialized.
>
> Anyway, I think I have an answer. There is a table in the database
> with >1.2 billion rows. Somehow loading any table from that database
> is triggering a scan of the very large table, perhaps all tables in
> the database. I dumped the smaller tables and made a new database and
> it is quick again.
>
> I will see if I can serialize the log output and report back.
>
> THK
>
> On Mon, Sep 12, 2011 at 7:59 PM, Tim Keitt <tkeitt at gmail.com> wrote:
>> The table has only a single row. Normally it loads instantly. Suddenly
>> its taking a very long time. Here's the output from postgres:
>>
>>
>> 2011-09-12 19:50:26 CDT STATEMENT:  select
>> estimated_extent('public','region_boundary','the_geom')
>> 2011-09-12 19:50:26 CDT LOG:  statement: select extent("the_geom")
>> from "public"."region_boundary"
>> 2011-09-12 19:50:26 CDT LOG:  duration: 0.519 ms
>> 2011-09-12 19:54:07 CDT LOG:  duration: 228246.691 ms
>> 2011-09-12 19:54:07 CDT LOG:  statement: BEGIN READ ONLY
>> 2011-09-12 19:54:07 CDT LOG:  duration: 0.145 ms
>> 2011-09-12 19:54:07 CDT LOG:  statement: declare qgisf0 binary cursor
>> for select "gid",asbinary("the_geom",'NDR') from
>> "public"."region_boundary" where "the_geom" &&
>> setsrid('BOX3D(-5826044.8984273653477430 -6024214.2690353775396943,
>> 7145528.0523074865341187 3985583.1240626471117139)'::box3d,900914)
>> 2011-09-12 19:54:08 CDT LOG:  duration: 73.467 ms
>> 2011-09-12 19:54:08 CDT LOG:  statement: fetch forward 200 from qgisf0
>> 2011-09-12 19:54:08 CDT LOG:  duration: 8.667 ms
>> 2011-09-12 19:54:08 CDT LOG:  statement: fetch forward 200 from qgisf0
>> 2011-09-12 19:54:08 CDT LOG:  duration: 0.107 ms
>> 2011-09-12 19:54:08 CDT LOG:  statement: CLOSE qgisf0
>> 2011-09-12 19:54:08 CDT LOG:  duration: 0.050 ms
>> 2011-09-12 19:54:08 CDT LOG:  statement: COMMIT
>> 2011-09-12 19:54:08 CDT LOG:  duration: 0.057 ms
>>
>> Anybody have an idea about this one?
>>
>> THK
>>
>>
>> --
>> Timothy H. Keitt
>> http://www.keittlab.org/
>>
>
>
>
> --
> Timothy H. Keitt
> http://www.keittlab.org/
>



-- 
Timothy H. Keitt
http://www.keittlab.org/