Re: Query runs too long for indexed tables
От | PFC |
---|---|
Тема | Re: Query runs too long for indexed tables |
Дата | |
Msg-id | op.s7i8n3lwcigqcu@apollo13 обсуждение исходный текст |
Ответ на | Re: Query runs too long for indexed tables ("Andrus" <eetasoft@online.ee>) |
Список | pgsql-performance |
> outer joins are not supported in Postgres UPDATE command. True (and sad). You can try the following script to play with the various options : DROP TABLE one; DROP TABLE two; CREATE TABLE one (a SERIAL PRIMARY KEY, b INT NULL); CREATE TABLE two (b INT NOT NULL PRIMARY KEY); INSERT INTO two (b) SELECT x*2 FROM generate_series( 1, 50000 ) AS x; INSERT INTO one (b) SELECT x FROM generate_series( 1, 100000 ) AS x; EXPLAIN ANALYZE SELECT count(*) FROM one LEFT JOIN two ON one.b=two.b WHERE two.b IS NULL; --Try with and without... --CREATE INDEX one_b ON one(b); VACUUM ANALYZE one; VACUUM ANALYZE two; EXPLAIN ANALYZE SELECT count(*) FROM one LEFT JOIN two ON one.b=two.b WHERE two.b IS NULL; BEGIN; EXPLAIN ANALYZE UPDATE one SET b=NULL WHERE b NOT IN (SELECT b FROM two ); SELECT * FROM one ORDER BY a LIMIT 5; ROLLBACK; VACUUM one; BEGIN; EXPLAIN ANALYZE UPDATE one SET b=NULL WHERE b IN (SELECT one.b FROM one LEFT JOIN two ON one.b=two.b WHERE two.b IS NULL); SELECT * FROM one ORDER BY a LIMIT 5; ROLLBACK; VACUUM one; BEGIN; EXPLAIN ANALYZE UPDATE one SET b=NULL FROM one x LEFT JOIN two ON x.b=two.b WHERE two.b IS NULL AND one.a=x.a; SELECT * FROM one ORDER BY a LIMIT 5; ROLLBACK; VACUUM one; BEGIN; EXPLAIN ANALYZE UPDATE one SET b=NULL FROM one x LEFT JOIN two ON x.b=two.b WHERE two.b IS NULL AND one.b=x.b; SELECT * FROM one ORDER BY a LIMIT 5; ROLLBACK; VACUUM one; BEGIN; EXPLAIN ANALYZE UPDATE one SET b=(SELECT two.b FROM two WHERE two.b=one.b); SELECT * FROM one ORDER BY a LIMIT 5; ROLLBACK; VACUUM one; BEGIN; EXPLAIN ANALYZE UPDATE one SET b=NULL WHERE NOT EXISTS (SELECT 1 FROM two WHERE two.b = one.b); SELECT * FROM one ORDER BY a LIMIT 5; ROLLBACK; VACUUM one; BEGIN; CREATE TABLE tmp AS SELECT one.a, two.b FROM one LEFT JOIN two ON one.b=two.b; SELECT * FROM tmp ORDER BY a LIMIT 5; DROP TABLE one; ALTER TABLE tmp RENAME TO one; ROLLBACK;
В списке pgsql-performance по дате отправления: