Thanks if you started to look into this, but nevermind. I figured it
out. Turns out I only needed to mark the function as STABLE:
http://www.postgresql.org/docs/8.0/static/xfunc-volatility.html
"A STABLE function cannot modify the database and is guaranteed to
return the same results given t
All,
I have a weird situation where my index IS used when I use a query that
hard-codes a value but it does NOT use the index when the value is
returned from a PGSQL function:
==
DOES NOT WORK
, Geoffrey M.
Cc: Tom Lane; pgsql-general@postgresql.org
Subject: Re: [GENERAL] Problem with index not being chosen inside PL/PgSQL
function...
On Dec 18, 2007 10:54 AM, Weber, Geoffrey M.
<[EMAIL PROTECTED]> wrote:
> Tom,
>
> Yes, the distribution must be what's doing it.
On Dec 18, 2007 10:54 AM, Weber, Geoffrey M.
<[EMAIL PROTECTED]> wrote:
> Tom,
>
> Yes, the distribution must be what's doing it. I guess I knew that
> subconciously, but was looking for something like hints to force the planner
> to do what I wanted. Instead it looks like I'll have to do a bit
From: Tom Lane [EMAIL PROTECTED]
Sent: Tuesday, December 18, 2007 10:36 AM
To: Weber, Geoffrey M.
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Problem with index not being chosen inside PL/PgSQL
function...
"Weber, Geoffrey M." <[EMAIL PROTECTED]> writes:
>
"Weber, Geoffrey M." <[EMAIL PROTECTED]> writes:
> Hmm - good question! However, it is - both the id and
> not_displayed_id are INTEGERs.
Well, in that case it must be a statistics issue --- does the indexed
column have a badly skewed distribution?
You could investigate how many rows the planner
he suggestion though!
From: Tom Lane [EMAIL PROTECTED]
Sent: Tuesday, December 18, 2007 10:11 AM
To: Weber, Geoffrey M.
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Problem with index not being chosen inside PL/PgSQL
function...
"Weber, Geoff
"Weber, Geoffrey M." <[EMAIL PROTECTED]> writes:
> The problem I'm having is in one particular spot where I'm trying to
> run a "parametized" query inside a PL/PgSQL function.
I wonder whether the parameter is actually of the same datatype as the
indexed column.
regards, t
Hi all,
Thanks to those on this list who contribute their knowledge for our
consumption! I have another question:
The problem I'm having is in one particular spot where I'm trying to run a
"parametized" query inside a PL/PgSQL function. When I run the query directly,
the planner correctly ch
Thanks for the thoughts, certainly I will look into what you have explained.
However, the behavior that you expressed isn't what is occuring. In the 12,
16 example 12 does have more rows than 16. However, there are many cases
when this isn't true, that is other states have more rows than 12 and th
"Rob Tester" <[EMAIL PROTECTED]> writes:
> SELECT * FROM STUFF WHERE state=12; --causes a seq scan of the table
> where
> SELECT * FROM STUFF WHERE state=16 --Uses the index.
This behavior is intended and appropriate, if there are lots of rows
with state=12 and not many with state=16. As an ex
I am using pg 8.1.4, I have a table with 1.1 million rows of data (see below
for table definition). One field state is numeric and has an index. The
index is not always picked up when searching the table by state only and I
can't figure out why.
So:
SELECT * FROM STUFF WHERE state=12; --causes
On mán, 2007-01-01 at 14:21 -0600, [EMAIL PROTECTED] wrote:
> Within the context of the function (after calling), these variables are
> constant and I'm attempting to use my OR syntax as shorthand to avoid
> having to use a dynamic statement *only* because of this situation.
> As I've mentioned,
Ragnar wrote:
Reguardless of the issue whether pl/pgsql could be expected to
optimize this case, I find it difficult to imagine a scenario
where this kind of coding makes sense.
I understand that in some cases on would like to do this with
a *variable* to simplify logic, but what possible gain c
[EMAIL PROTECTED] writes:
> I was referring to the situation where one has this in a WHERE clause:
>((vConstant IS NULL) OR (Table.IndexedCol = vConstant))
> where vConstant is a *constant* parameter in a pl/pgsql function.
My mistake, I was thinking of Table.IndexedCol IS NULL.
> In the late
On mið, 2006-12-27 at 11:02 -0600, [EMAIL PROTECTED] wrote:
>
> I was referring to the situation where one has this in a WHERE clause:
>
>((vConstant IS NULL) OR (Table.IndexedCol = vConstant))
>
> where vConstant is a *constant* parameter in a pl/pgsql function.
Reguardless of the issue wh
Tom Lane wrote:
[EMAIL PROTECTED] writes:
I would submit that in that situation, it would be
reasonable for a user to expect my suggested syntax to still use the
indicated indexes.
The only thing that will make that work is if "indexed_col IS NULL" were
an indexable condition, which it isn't
[EMAIL PROTECTED] writes:
> I would submit that in that situation, it would be
> reasonable for a user to expect my suggested syntax to still use the
> indicated indexes.
The only thing that will make that work is if "indexed_col IS NULL" were
an indexable condition, which it isn't because the P
[EMAIL PROTECTED] wrote:
Tom Lane wrote:
you're still gonna lose because those are variables not constants ...
Well, that *is* what I'm hoping to do. I understand how (0 IS NULL) is
different from (variable IS NULL), but isn't it reasonable to expect
that PG could evaluate that expression o
Tom Lane wrote:
Well, you could update --- 8.2 contains code to recognize that the IS
NULL expression is constant, but prior releases do not.
That's excellent to hear -- I'd missed that in my perusing of the
changelogs between 8.0.x and 8.2. That does give me one more reason to
upgrade. It
[EMAIL PROTECTED] writes:
>select * from t where c1 = 75000;
>select * from t where ((0 is null) OR (c1 = 75000));
> The first one properly uses the index on c1, the second does not.
> Obviously, a human looking at the second one would realize it's
> essentially identical to the first an
Hello,
I've been using PostgreSQL for a few years and mostly love it. Aside
from a few (perceived, anyway) annoying limitations in PL/PGSQL (which I
almost exclusively am using for db interaction), I'm very satisfied with it.
I ran across this problem several months back and decided to blow
Hello all, I have a problem.
When a try to use EXPLAIN
The optimize don't user the index of primary key.
Why ?
Thanks
Eriko
23 matches
Mail list logo
