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

[Andreas Neumann]

Hi Tim,

It sounds to me like it is trying to determine the primary key.

Does it load quicker if you manually specify the primary key column?

It sounds strange to me though that a scan is already happening on the
other tables, unless they are connected (say through a join or foreign
key or something else).

Andreas

On 09/13/2011 04:15 AM, Tim Keitt wrote:
> 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/
>>
> 
> 
>