7.3 support for IN
От | Gregory Wood |
---|---|
Тема | 7.3 support for IN |
Дата | |
Msg-id | 009601c22e85$7e9335a0$7889ffcc@comstock.com обсуждение исходный текст |
Ответы |
Re: 7.3 support for IN
|
Список | pgsql-general |
Before anyone starts thinking it, yes, I am aware that INs are really slow in PostgreSQL. That being said... I was doing some tests with big IN statements and various workarounds and was shocked at some of the results. Here are the test cases I came up with: 1. Single value (16 ms) 2. 2 values using IN (870 ms) 3. 2 values using OR (870 ms) 4. 2 values using EXISTS (840 ms) 5. 2 values using JOIN (760 ms) 6. 2 values using UNION (31 ms) 7. 44 values using IN (960 ms) 8. 44 values using OR (960 ms) 9. 44 values using EXISTS (880 ms) 10. 44 values using JOIN (930 ms) 11. 44 values using UNION (670 ms) Note that the EXISTS and the JOIN do not count the time needed to execute the CREATE TABLE or COPY statements needed to populate a separate dataset to query across. Now then, it concerns me that doing 44 separate SELECTs and UNIONing the results together is faster than simply doing an IN or an OR. Heck, it's significantly faster to do 44 UNIONs than it is to do (Field=Value1 OR Field=Value2) with just *2* values. Is that really to be expected or am I doing a really poor job of optimizing/querying? Is this going to be improved in 7.3? I've attached the schema and the queries used to do my very rough benchmarking. The two tables have 591655 and 3044478 rows. If you would like to see anything else, please let me know. Greg
Вложения
В списке pgsql-general по дате отправления: