Opened 12 years ago

Closed 12 years ago

#2082 closed enhancement (fixed)

Add index on edge_data.{start,end}_node

Reported by: strk Owned by: strk
Priority: medium Milestone: PostGIS 2.0.2
Component: topology Version: 2.0.x
Keywords: history Cc:

Description

Since start_node and end_node are foreign keys to node.node_id, removing isolated nodes becomes a very expensive operation when those fields are not indexed as they require a sequencial scan on the whole edges table for each DELETE.

I've been waiting more than 30 minutes before killing a DELETE query aimed at cleaning up a topology…

Change History (3)

comment:1 by strk, 12 years ago

NOTE: it completes in 3 seconds after the index is built!

comment:2 by strk, 12 years ago

Milestone: PostGIS 2.1.0 → PostGIS 2.0.2

for the record: this was on PostgreSQL 9.1.6 with postgis 2.0.2SVN r10402

comment:3 by strk, 12 years ago

Keywords: history added
Resolution: → fixed
Status: new → closed
Type: defect → enhancement

r10643 in 2.0, r10644 in trunk

Note: See TracTickets for help on using tickets.