Em 28 de junho de 2011 20:28, Fabrízio de Royes Mello
<[email protected]> escreveu:
>
> Em 28 de junho de 2011 20:15, Dickson S. Guedes <[email protected]>
> escreveu:
>>
>> Uma outra seria:
>>
>> SELECT i
>> FROM generate_series(1,(SELECT max(v) FROM foo)) AS i
>> LEFT JOIN foo ON (v = i)
>> WHERE v IS NULL;
>>
>
>
> Ou ainda:
> SELECT i
> FROM generate_series(1,(SELECT max(v) FROM foo)) AS i
> WHERE NOT EXISTS (SELECT 1 FROM foo WHERE v = i)
>
> ;-)
O que internamente é o mesmo ;)
Apenas para demonstrar, os planos de execução de ambas as consultas:
postgres=# explain ANALYZE SELECT i
FROM generate_series(1,(SELECT max(v) FROM foo)) AS i
LEFT JOIN foo ON (v = i)
WHERE v IS NULL;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------
Hash Anti Join (cost=1713.84..1736.34 rows=500 width=4) (actual
time=114.513..182.061 rows=6541 loops=1)
Hash Cond: (i.i = pg_temp_2.foo.v)
InitPlan 1 (returns $0)
-> Aggregate (cost=657.23..657.24 rows=1 width=4) (actual
time=48.378..48.379 rows=1 loops=1)
-> Seq Scan on foo (cost=0.00..557.38 rows=39938 width=4)
(actual time=0.171..24.683 rows=13397 loops=1)
-> Function Scan on generate_series i (cost=0.00..10.00 rows=1000
width=4) (actual time=59.191..82.971 rows=15084 loops=1)
-> Hash (cost=557.38..557.38 rows=39938 width=4) (actual
time=55.288..55.288 rows=13397 loops=1)
Buckets: 4096 Batches: 1 Memory Usage: 314kB
-> Seq Scan on foo (cost=0.00..557.38 rows=39938 width=4)
(actual time=0.115..25.912 rows=13397 loops=1)
Total runtime: 193.075 ms
(10 rows)
postgres=# explain ANALYZE SELECT i
FROM generate_series(1,(SELECT max(v) FROM foo)) AS i
WHERE NOT EXISTS (SELECT 1 FROM foo WHERE v = i);
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------
Hash Anti Join (cost=1713.84..1736.34 rows=500 width=4) (actual
time=115.860..183.533 rows=6541 loops=1)
Hash Cond: (i.i = pg_temp_2.foo.v)
InitPlan 1 (returns $0)
-> Aggregate (cost=657.23..657.24 rows=1 width=4) (actual
time=49.114..49.116 rows=1 loops=1)
-> Seq Scan on foo (cost=0.00..557.38 rows=39938 width=4)
(actual time=0.171..25.021 rows=13397 loops=1)
-> Function Scan on generate_series i (cost=0.00..10.00 rows=1000
width=4) (actual time=59.835..83.513 rows=15084 loops=1)
-> Hash (cost=557.38..557.38 rows=39938 width=4) (actual
time=55.991..55.991 rows=13397 loops=1)
Buckets: 4096 Batches: 1 Memory Usage: 314kB
-> Seq Scan on foo (cost=0.00..557.38 rows=39938 width=4)
(actual time=0.116..25.896 rows=13397 loops=1)
Total runtime: 194.518 ms
(10 rows)
[]s
--
Dickson S. Guedes
mail/xmpp: [email protected] - skype: guediz
http://guedesoft.net - http://www.postgresql.org.br
_______________________________________________
pgbr-geral mailing list
[email protected]
https://listas.postgresql.org.br/cgi-bin/mailman/listinfo/pgbr-geral
