I've reworked a bit an application to make 1 query (with many OR) instead of thousands (2900 in this bench),
but was a bit disappointed with the perf. Here follow a quick bench and 2 questions at the end:
Here is my table CREATE TABLE properties ( item_id text NOT NULL, calendar_id uuid NOT NULL, clef text, valeur text, recurrence_id bigint, CONSTRAINT fk_props_id FOREIGN KEY (item_id, calendar_id, recurrence_id) REFERENCES cal_item (itemid, calendar_id, recurrence_id) MATCH SIMPLE ON UPDATE CASCADE ON DELETE CASCADE )
Here are 3 versions of the "same" query, there is ~2900 conditions (OR, UNION, UNION ALL), returning ~9000 lines (same exact number each time)
(i've ran the tests multiple time, using postgresql 9.3.5 on rhel 6, on an idle physical server)
1) SELECT clef,valeur,ITEM_ID,CALENDAR_ID,RECURRENCE_ID FROM PROPERTIES WHERE (ITEM_ID=((E'1263d4ac-d8a3-44d7-b971-5eee947ff36a')::text) AND CALENDAR_ID=((E'f9532857-ca68-497b-b2a9-162507110e55')::uuid) AND RECURRENCE_ID=((0)::int8)) OR (ITEM_ID=((E'9a1e40bb-0280-4e79-ae13-ee57d614fdef')::text) AND CALENDAR_ID=((E'f9532857-ca68-497b-b2a9-162507110e55')::uuid) AND RECURRENCE_ID=((0)::int8)) OR .....
total time: 7.6s
plan: Index Cond, Bitmap index scan on fk_props_id, BitmapOr, Recheck Cond, Bitmap Heap Scan on properties
2) SELECT clef,valeur,ITEM_ID,CALENDAR_ID,RECURRENCE_ID FROM PROPERTIES WHERE (ITEM_ID=((E'1263d4ac-d8a3-44d7-b971-5eee947ff36a')::text) AND CALENDAR_ID=((E'f9532857-ca68-497b-b2a9-162507110e55')::uuid) AND RECURRENCE_ID=((0)::int8)) UNION SELECT clef,valeur,ITEM_ID,CALENDAR_ID,RECURRENCE_ID FROM PROPERTIES WHERE (ITEM_ID=((E'9a1e40bb-0280-4e79-ae13-ee57d614fdef')::text) AND CALENDAR_ID=((E'f9532857-ca68-497b-b2a9-162507110e55')::uuid) AND RECURRENCE_ID=((0)::int8)) UNION ...
total time: 1s
plan: Index Cond, Index Scan using fki_props_id on properties properties_XXX, Append, HashAggregate
3) SELECT clef,valeur,ITEM_ID,CALENDAR_ID,RECURRENCE_ID FROM PROPERTIES WHERE (ITEM_ID=((E'1263d4ac-d8a3-44d7-b971-5eee947ff36a')::text) AND CALENDAR_ID=((E'f9532857-ca68-497b-b2a9-162507110e55')::uuid) AND RECURRENCE_ID=((0)::int8)) UNION ALL SELECT clef,valeur,ITEM_ID,CALENDAR_ID,RECURRENCE_ID FROM PROPERTIES WHERE (ITEM_ID=((E'9a1e40bb-0280-4e79-ae13-ee57d614fdef')::text) AND CALENDAR_ID=((E'f9532857-ca68-497b-b2a9-162507110e55')::uuid) AND RECURRENCE_ID=((0)::int8)) UNION ALL ...
total time: 1.2s
plan: Index Cond, Index Scan using fki_props_id on properties properties_XXX, Append
Two questions:
1) Is it normal to have such a big difference between OR and UNION and should i always prefer UNION when possible?
2) How can UNION ALL be slower than UNION, it's not doing the HashAggregate but is 0.2s slower?
