Skip site navigation (1) Skip section navigation (2)

BUG #4097: can join + where query be optimized?

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 (view raw or flat)
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

pgsql-bugs by date

Next:From: J6MDate: 2008-04-07 12:44:55
Subject: Re: BUG #4096: PG 8.3.1. confused about remaining disk space
Previous:From: Gregory StarkDate: 2008-04-07 11:14:39
Subject: Re: BUG #4096: PG 8.3.1. confused about remaining disk space

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group