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

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

pgsql-performance by date

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

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