From: | "Adriaan van Kekem" <adriaanvk(at)gmail(dot)com> |
---|---|
To: | pgsql-bugs(at)postgresql(dot)org |
Subject: | BUG #4097: can join + where query be optimized? |
Date: | 2008-04-07 11:34:50 |
Message-ID: | 200804071134.m37BYoSG070240@wwwmaster.postgresql.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs |
The following bug has been logged online:
Bug reference: 4097
Logged by: Adriaan van Kekem
Email address: adriaanvk(at)gmail(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | J6M | 2008-04-07 12:44:55 | Re: BUG #4096: PG 8.3.1. confused about remaining disk space |
Previous Message | Gregory Stark | 2008-04-07 11:14:39 | Re: BUG #4096: PG 8.3.1. confused about remaining disk space |