Thread: Updating a table column with ref integrity
I need to to change the type of a table column. The problem is that this table has a ref integrity constarint set up withanother table. Does anyone know how I can do this easily without breaking any constraints. thanks
Teknokrat, > I need to to change the type of a table column. The problem is that > this > table has a ref integrity constarint set up with another table. Does > > anyone know how I can do this easily without breaking any > constraints. Easily? No. In sum: dump your database to file. Backup. Edit the file. Re-create and re-load the database. -Josh Berkus
Hi--
I have this rather long complex query that takes really long to complete
(please see below). It seems like I ought to improve the speed somehow.
I don't understand, for example, what the query planner is doing when it
says "Hash" and why this appears to take so long. And since I have a key
for Visitor.ID, I don't understand why its doing a sequential scan on
that table...
Any advice would be greatly appreciated!
Thanks
Peter
EXPLAIN SELECT
"Visitor"."Created","Visitor"."Updated","Tidbit"."ID","ProgramEvent"."ID","Visitor"."Email","Interest"."ID","VisitorInternetDeviceAssoc"."ID","Referral"."ID"
FROM "VisitorExtra"
LEFT OUTER JOIN Tidbit" ON "VisitorExtra"."ID"="Tidbit"."VisitorID"
LEFT OUTER JOIN "ProgramEvent" ON"VisitorExtra"."ID"="ProgramEvent"."VisitorID"
LEFT OUTER JOIN "Interest" ON "VisitorExtra"."ID"="Interest"."VisitorID"
LEFT OUTER JOIN "VisitorInternetDeviceAssoc" ON"VisitorExtra"."ID"="VisitorInternetDeviceAssoc"."VisitorID"
LEFT OUTER JOIN "Referral" ON"VisitorExtra"."ID"="Referral"."FromVisitorID","Visitor"
WHERE "VisitorExtra"."ID"="Visitor"."ID" AND "VisitorExtra"."ID"= 325903;
NOTICE: QUERY PLAN:
Hash Join (cost=14584.37..59037.79 rows=57747 width=76) -> Merge Join (cost=0.00..36732.65 rows=57747 width=44)
-> Merge Join (cost=0.00..29178.16 rows=10681 width=36) -> Nested Loop (cost=0.00..10505.74 rows=6674
width=28) -> Nested Loop (cost=0.00..435.29 rows=177 width=20) ->
NestedLoop (cost=0.00..15.70 rows=55 width=12) -> Index Scan using
VisitorExtra_pkey on VisitorExtra (cost=0.00..3.01 rows=1 width=4)
-> Index Scan using
Tidbit_VisitorID_key on Tidbit (cost=0.00..12.67 rows=2
width=8) -> Index Scan using
ProgramEvent_VisitorID_key on ProgramEvent (cost=0.00..7.57
rows=2 width=8) -> Index Scan using Interest_VisitorID_key on Interest (cost=0.00..56.66
rows=19width=8) -> Index Scan using VisitorInternetDeviceAssoc_Visi on VisitorInternetDeviceAssoc
(cost=0.00..16402.90rows=174887
width=8) -> Index Scan using Referral_FromVisitorID_key on Referral (cost=0.00..6323.41 rows=87806 width=8)
-> Hash (cost=6061.79..6061.79 rows=317379 width=32) -> Seq Scan on Visitor (cost=0.00..6061.79 rows=317379
width=32)
Problem could be the SeqScan on visitor (very last line of explain). This appears to be coming from the last join in your query. Does an index on ID exist on Visitor table? Does changing: WHERE "VisitorExtra"."ID"="Visitor"."ID" AND "VisitorExtra"."ID"= 325903; to WHERE "VisitorExtra"."ID"="Visitor"."ID" AND "Visitor"."ID"= 325903; have any effect? Why do you use "From VisitorExtra" in first select when no result fields are in the table? Why not replace all references to "VisitorExtra"."ID"to "Visitor"."ID"? Frank At 03:33 PM 11/15/02, Peter T. Brown wrote: >Hi-- > >I have this rather long complex query that takes really long to complete >(please see below). It seems like I ought to improve the speed somehow. >I don't understand, for example, what the query planner is doing when it >says "Hash" and why this appears to take so long. And since I have a key >for Visitor.ID, I don't understand why its doing a sequential scan on >that table... > >Any advice would be greatly appreciated! > > >Thanks > >Peter > > > > >EXPLAIN SELECT > "Visitor"."Created", > "Visitor"."Updated", > "Tidbit"."ID", > "ProgramEvent"."ID", > "Visitor"."Email", > "Interest"."ID", > "VisitorInternetDeviceAssoc"."ID", > "Referral"."ID" > >FROM "VisitorExtra" > >LEFT OUTER JOIN Tidbit" ON > "VisitorExtra"."ID"="Tidbit"."VisitorID" > >LEFT OUTER JOIN "ProgramEvent" ON > "VisitorExtra"."ID"="ProgramEvent"."VisitorID" > >LEFT OUTER JOIN "Interest" ON > "VisitorExtra"."ID"="Interest"."VisitorID" > >LEFT OUTER JOIN "VisitorInternetDeviceAssoc" ON > "VisitorExtra"."ID"="VisitorInternetDeviceAssoc"."VisitorID" > >LEFT OUTER JOIN "Referral" ON > "VisitorExtra"."ID"="Referral"."FromVisitorID","Visitor" > >WHERE "VisitorExtra"."ID"="Visitor"."ID" AND > "VisitorExtra"."ID"= 325903; > > >NOTICE: QUERY PLAN: > >Hash Join (cost=14584.37..59037.79 rows=57747 width=76) > -> Merge Join (cost=0.00..36732.65 rows=57747 width=44) > -> Merge Join (cost=0.00..29178.16 rows=10681 width=36) > -> Nested Loop (cost=0.00..10505.74 rows=6674 width=28) > -> Nested Loop (cost=0.00..435.29 rows=177 > width=20) > -> Nested Loop (cost=0.00..15.70 rows=55 > width=12) > -> Index Scan using VisitorExtra_pkey > on VisitorExtra (cost=0.00..3.01 > rows=1 width=4) > -> Index Scan > using >Tidbit_VisitorID_key on >Tidbit (cost=0.00..12.67 rows=2 >width=8) > -> Index Scan > using >ProgramEvent_VisitorID_key on >ProgramEvent (cost=0.00..7.57 >rows=2 width=8) > -> Index Scan using Interest_VisitorID_key on > Interest (cost=0.00..56.66 rows=19 width=8) > -> Index Scan using VisitorInternetDeviceAssoc_Visi on > VisitorInternetDeviceAssoc (cost=0.00..16402.90 > rows=174887 >width=8) > -> Index Scan using Referral_FromVisitorID_key on Referral > (cost=0.00..6323.41 rows=87806 width=8) > -> Hash (cost=6061.79..6061.79 rows=317379 width=32) > -> Seq Scan on Visitor (cost=0.00..6061.79 rows=317379 >width=32) > > > >---------------------------(end of broadcast)--------------------------- >TIP 6: Have you searched our list archives? > >http://archives.postgresql.org