Re: Restore referencial integrity

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(at)fetter(dot)org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david(dot)fetter(at)gmail(dot)com
iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate