Re: Optimizer is not choosing index

From: Markus Innerebner <markus(dot)innerebner(at)inf(dot)unibz(dot)it>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: Optimizer is not choosing index
Date: 2012-02-16 06:54:37
Message-ID: 74FBA69B-5697-4FDF-98A2-038BBD628126@inf.unibz.it
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

Hi Tom,

thanks for your suggestion:

> Markus Innerebner <markus(dot)innerebner(at)inf(dot)unibz(dot)it> writes:
>> The query plan says, that a sequential scan is performed on the edge table. I consider it strange that he is not accessing on the (btree) index one the edge table.
>
> This suggests that you have a datatype mismatch:
>
>> " Hash Cond: ((e.target)::numeric = n.id)"
>
> Your index is presumably on e.target, not e.target::numeric, so it's not
> applicable. Try to make the join columns the same datatype.

indeed: the id column in the node table had as type numeric, while in edges the target is integer.

After changing it, the index is used again.

many thanks

cheers Markus

In response to

Browse pgsql-performance by date

  From Date Subject
Next Message Alessandro Gagliardi 2012-02-17 18:34:50 Why so slow?
Previous Message Han Zhou 2012-02-16 01:44:53 Re: Fwd: [HACKERS] client performance v.s. server statistics