From: Tom Lane <tgl@sss.pgh.pa.us>
To: Jeremy Palmer <JPalmer@linz.govt.nz>
Cc: pgsql-performance@postgresql.org <pgsql-performance@postgresql.org>
Subject: Re: Index usage with functions in where condition
Date: Fri, 09 Jul 2010 19:19:50 -0400
Message-ID: <2741.1278717590@sss.pgh.pa.us> (raw)
In-Reply-To: <666FB8D75E95AE42965A0E76A5E5337E065C035C1E@prdlsmmsg01.ad.linz.govt.nz>
References: <666FB8D75E95AE42965A0E76A5E5337E065C035C1E@prdlsmmsg01.ad.linz.govt.nz>
Jeremy Palmer <JPalmer@linz.govt.nz> writes:
> This is the query that does not use the indexes:
> SELECT
> coo.nod_id,
> 6400000*radians(sqrt((coo.value1 - -41.0618)^2+((coo.value2 - 175.58461)*cos(radians(-41.0618)))^2)) as distance
> FROM
> crs_coordinate coo
> WHERE
> coo.value1 between -41.0618-degrees(1200.0/6400000.0) and -41.0618+degrees(1200.0/6400000.0) and
> coo.value2 between 175.58461-degrees(1200.0/6400000.0)/(cos(radians(-41.0618))) and 175.58461+degrees(1200.0/6400000.0)/(cos(radians(-41.0618)));
Those expressions yield float8, not numeric, and numeric vs float8 isn't
an indexable operator for reasons we needn't get into here. You should
probably rethink whether numeric is really the best choice of datatype
for your columns, if this is the sort of value you expect to work with
--- you're paying a considerable price in speed and space for
perhaps-illusory precision gains. But if you insist on using numeric
then the solution is to cast the expression results to numeric
explicitly.
BTW I wonder whether you ought not be looking into postgis rather than
rolling-your-own coordinate arithmetic ...
regards, tom lane
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: tgl@sss.pgh.pa.us, JPalmer@linz.govt.nz
Subject: Re: Index usage with functions in where condition
In-Reply-To: <2741.1278717590@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox