The following bug has been logged online:
Bug reference: 4097
Logged by: Adriaan van Kekem
Email address: adriaanvk@gmail.com
PostgreSQL version: 8.1
Operating system: Ubuntu 7.04
Description: can join + where query be optimized?
Details:
In the following query you see 2 times the same part between ###. if i ONLY
set this filter by the where, the query is not performing at all. If i add
the filter by both the join condition and at the where part, it is
performing!
Can this be a standard optimization in postgres? We where thinking of the
procedure:
- check if the where can be done on an index
- if yes, add the condition also to the join condition.
What do u guys think of this?
SELECT "Zoekenclientbooster_data".data, "Zoekenclientbooster_data".iid,
"Zoekenclientbooster_data".locked
FROM "Zoekenclientbooster_data" inner join "Zoekenclientbooster_keys" as
sortkeys on
sortkeys.iid = "Zoekenclientbooster_data".iid
### and sortkeys.name='burgerservicenummer' ###
inner join "Zoekenclientbooster_keys" as "searchkeyseinddatum" on
sortkeys.iid = "searchkeyseinddatum".iid
and "searchkeyseinddatum".name = 'einddatum' AND
("searchkeyseinddatum".value_index >= '/2008|s04|s07/') inner join
"Zoekenclientbooster_keys" as "searchkeysmedewerkeriid" on sortkeys.iid =
"searchkeysmedewerkeriid".iid
and "searchkeysmedewerkeriid".name = 'medewerkeriid' AND
("searchkeysmedewerkeriid".value_index = '//') inner join
"Zoekenclientbooster_keys" as "searchkeysnaam" on sortkeys.iid =
"searchkeysnaam".iid
and "searchkeysnaam".name = 'naam' AND
("searchkeysnaam".value_index LIKE '/jansen%/')
WHERE
### sortkeys.name='burgerservicenummer' ###
order by sortkeys.value_index ASC LIMIT 9 OFFSET 0