I know the intensity of ilikes but I don't see another way to solve it.
But that shouldn't be the problem because the query runs on another
server (not as powerful as the actual machine) with postgres 8.3 in
acceptable time (same data, same query).
Each of the collumns of the relation table has an index on it
(relation_id, v and k).
I thought about differences in joining strategy between 8.3 and 8.4.
Becaus there are some posts in this group about join problems with > 8.4
(but not sure)? As mentioned before the 8.4 query plan differs from the
8.3 query plan (same query).
8.3 query plan: http://explain.depesz.com/s/KdF (no problem)
8.4 query plan: http://explain.depesz.com/s/dO7 (problem query)
Jo
On 15.03.2011 17:24, Merlin Moncure wrote:
> On Mon, Mar 14, 2011 at 9:48 AM, Jo<jl.news@uni-bonn.de> wrote:
>> I set the work_mem to 100MB and the shared buffers are 2 GB
>>
>> The query plans are long and complex. I send the beginning of the
>> two plans. Hope this helps to understand the differences.
>> I assume the join strategy in 8.3 differs from the one in 8.4.
>>
>>
>> *************************************
>> The beginning of the 8.4:
>> *************************************
>> "Seq Scan on relations (cost=0.00..1502557856.52 rows=332613 width=24)"
>> " Filter: (((SubPlan 36) OR (SubPlan 37)) AND (SubPlan 38))"
>> " SubPlan 1"
>> " -> Index Scan using idx_relation_tags_relation_id on relation_tags
>> (cost=0.00..8.97 rows=1 width=0)"
>> " Index Cond: (relation_id = $0)"
>> " Filter: ((k ~~* 'name'::text) AND (v !~~* ''::text))"
>> " SubPlan 2"
>
>
> well, regardless of the version, you're doing a gazillion sequential
> scans on relation tags. This looks like the primary culprit (I had to
> look up the ~~* operator...it's 'ilike'):
> (
> (k ~~* 'boundary'::text) OR
> (
> (k ~~* 'type'::text)
> AND (v ~~* 'boundary'::text)
> AND (relation_id = $0)
> )
> )
>
> 1. do we really. really need to be using ~~* here? how about '~~' (like) or '='
> 2. can we see definition and indexes on relation_tags? In particular,
> have you considered an index on (k,v,relation_id), or maybe one on
> (relation_id, v, k) and one on k?
> 3. can we see the source query?
>
> merlin
>
