On Mon, Aug 30, 2010 at 05:04:36PM -0300, Carlos Henrique Reimer wrote: > Hi > > Thank David and Georg for your suggestions. > > Yes, there is an index now defined on column protocolo in table > posicoes_controles. Legal! > I've selected two suggested commands to compare which would be more > performatic and which will run faster: > > Option 1) > explain delete from posicoes_controles where protocolo not in (select > protocolo from posicoes); > "Seq Scan on posicoes_controles (cost=9954587.42..1185225908771206.50 > rows=189513428 width=6)" > " Filter: (NOT (subplan))" > " SubPlan" > " -> Materialize (cost=9954587.42..15255636.80 rows=381199038 width=4)" > " -> Seq Scan on posicoes (cost=0.00..8084329.38 rows=381199038 > width=4)" > > Option 2) > explain delete FROM posicoes_controles WHERE NOT EXISTS ( > SELECT 1 FROM posicoes WHERE posicoes.protocolo = > posicoes_controles.protocolo > ); > "Seq Scan on posicoes_controles (cost=0.00..9560672015.05 rows=189419047 > width=6)" > " Filter: (NOT (subplan))" > " SubPlan" > " -> Index Scan using pk_posicoes_protocolo on posicoes > (cost=0.00..25.19 rows=1 width=0)" > " Index Cond: (protocolo = $0)" > I'm not an explain specialist but I understood the second option will run > much more faster. It probably will. EXISTS returns immediately when it finds the first row. > Let me know if I understood the explain for the second option: > 1) Run a seq scan on posicoes_controles and get the protocolo key to access > posicoes_protocolo > 2) For each row accessed in item 1 run an index scan on posicoes to check if > the key > is in the table posicoes > 3) If the parent found is not found on posicoes then remove the row from > posicoes_controles > > Am I thinking correctly? I believe so. Cheers, David (whose pt_BR is pretty w34k) -- David Fetter <david@xxxxxxxxxx> http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fetter@xxxxxxxxx iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
