pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Index not used in functions in 7.0?
7+ messages / 4 participants
[nested] [flat]

* Index not used in functions in 7.0?
@ 2000-05-16 15:26  Kyle Bateman <kyle@actarg.com>
  0 siblings, 2 replies; 7+ messages in thread

From: Kyle Bateman @ 2000-05-16 15:26 UTC (permalink / raw)
  To: pgsql-sql; +Cc: tgl@sss.pgh.pa.us; merrill@actarg.com

Help! We upgraded to 7.0 last week and all looks good except a problem
with indexes and its a show-stopper!  Any advice would be greatly
appreciated.

Here's the problem.  I have a table with 50,000 entries.  This function
runs a sum on a column of the table:

create function summtr_ocpt2(text, int4) returns int4 as '
    select sum(tquant) from mtr_reg where to_proj = $2 and pnum = $1 and
(status = \'open\' or status = \'clsd\' or status = \'prip\');
    ' language 'sql';

We have a critical query (which calls this function) we run on stock
levels that used to take about 30 seconds.  Now it takes something like
30 hours (I've never seen it terminate).

The problem seems to be that when a query is called from within a
function, the scan is sequential even if an index exists.  I tried
entering the SQL directly as:

select sum(tquant)
        from mtr_reg
        where to_proj = 50
        and pnum = '1051'
        and (status = 'clsd' or status = 'open' or status = 'prip')
;

And it accesses the index properly even though it is the exact same
query.

I dumped the log (-d 9) from the postmaster and the plan confirms that
the scan is sequential when called from within the function but indexed
when the SQL is called directly.

Is this due to something I'm doing wrong or is this a bug?

I'd be happy to provide a dump of the data, do other testing or whatever
would help.  I'm not sure who on the team is best to look at this.

Thanks all!

Kyle

Attachments:

kyle.vcf (text/x-vcard, 290B) download begin:vcard n:Bateman;Kyle tel;fax:801-377-8096 tel;work:801-377-8033x101 x-mozilla-html:FALSE url:www.actiontarget.com org:Action Target Inc adr:;;PO Box 636;Provo;UT;84603;US version:2.1 email;internet:kyle@actiontarget.com title:President x-mozilla-cpt:;-15520 fn:Kyle Bateman end:vcard
^ permalink raw reply [nested|flat] 7+ messages in thread

* Re: Index not used in functions in 7.0?
@ 2000-05-16 15:50  Tom Lane <tgl@sss.pgh.pa.us>
  parent: Kyle Bateman <kyle@actarg.com>
  1 sibling, 1 reply; 7+ messages in thread

From: Tom Lane @ 2000-05-16 15:50 UTC (permalink / raw)
  To: Kyle Bateman <kyle@actarg.com>; +Cc: pgsql-sql; merrill@actarg.com

Kyle Bateman <kyle@actarg.com> writes:
> I dumped the log (-d 9) from the postmaster and the plan confirms that
> the scan is sequential when called from within the function but indexed
> when the SQL is called directly.

Yikes, that does sound like a bug.

> I'd be happy to provide a dump of the data, do other testing or whatever
> would help.  I'm not sure who on the team is best to look at this.

How big would the dump file be?  I'm willing to look at this, and if the
dump isn't very large then it'd be easier to just install your DB here
instead of trying to gin up my own test case.

			regards, tom lane



^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: Index not used in functions in 7.0?
@ 2000-05-16 16:36  Tom Lane <tgl@sss.pgh.pa.us>
  parent: Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 2 replies; 7+ messages in thread

From: Tom Lane @ 2000-05-16 16:36 UTC (permalink / raw)
  To: Kyle Bateman <kyle@actarg.com>; pgsql-sql; merrill@actarg.com

Tom Lane <tgl@sss.pgh.pa.us> writes:
> Kyle Bateman <kyle@actarg.com> writes:
>> I dumped the log (-d 9) from the postmaster and the plan confirms that
>> the scan is sequential when called from within the function but indexed
>> when the SQL is called directly.

> Yikes, that does sound like a bug.

Actually, after further thought I realize that there is a reason for
plans within functions to be different from plans of hand-entered
queries.  In the latter case the optimizer knows the constant values
(eg, it sees "WHERE to_proj = 50"), in the former case it doesn't
(eg, it sees "WHERE to_proj = $1") and has to fall back on guesses
about selectivities.

In the particular case at hand I've have expected it to pick an
indexscan anyway, but maybe there's just something weird about your
data.  Could I trouble you for the vacuum stats for that table?
Easiest way to get them is

select attname,attdisbursion,s.*
from pg_statistic s, pg_attribute a, pg_class c
where starelid = c.oid and attrelid = c.oid and staattnum = attnum
and relname = 'FOO';

(I'm beginning to think we should create a standard system view
for this query ;-))

			regards, tom lane



^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: Index not used in functions in 7.0?
@ 2000-05-16 18:00  mig@utdt.edu
  parent: Tom Lane <tgl@sss.pgh.pa.us>
  1 sibling, 1 reply; 7+ messages in thread

From: mig@utdt.edu @ 2000-05-16 18:00 UTC (permalink / raw)
  To: tgl@sss.pgh.pa.us; +Cc: kyle@actarg.com; pgsql-sql; merrill@actarg.com

I am probably completely wrong, but at least it will be short ...

Could it be that
    select sum(tquant)
        from mtr_reg
        where to_proj = $2
        and pnum = $1
        and (status = 'clsd' or status = 'open' or status = 'prip')
    ;
makes it harder for the optimizer due to the ORs, and that
    select sum(tquant)
        from mtr_reg
        where to_proj = $2
        and pnum = $1
        and status in ('clsd','open','prip')
    --- last line changed
    ;
would produce a better cost estimate? 

Miguel Sofer




^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: Index not used in functions in 7.0?
@ 2000-05-16 18:13  Kyle Bateman <kyle@actarg.com>
  parent: mig@utdt.edu
  0 siblings, 0 replies; 7+ messages in thread

From: Kyle Bateman @ 2000-05-16 18:13 UTC (permalink / raw)
  To: mig@utdt.edu; +Cc: pgsql-sql

mig@utdt.edu wrote:

> I am probably completely wrong, but at least it will be short ...
>
> Could it be that
>     select sum(tquant)
>         from mtr_reg
>         where to_proj = $2
>         and pnum = $1
>         and (status = 'clsd' or status = 'open' or status = 'prip')
>     ;
> makes it harder for the optimizer due to the ORs, and that
>     select sum(tquant)
>         from mtr_reg
>         where to_proj = $2
>         and pnum = $1
>         and status in ('clsd','open','prip')
>     --- last line changed
>     ;
> would produce a better cost estimate?
>
> Miguel Sofer

Thanks for the input.  Your point might be correct, but my results are
the same regardless of the clause attached to the status field.  In one
of my tests, the query simply had "and status = 'clsd'."  The issue here
is that a sequential scan is being chosen over an indexed scan when the
query is run from within a function.

Attachments:

kyle.vcf (text/x-vcard, 290B) download begin:vcard n:Bateman;Kyle tel;fax:801-377-8096 tel;work:801-377-8033x101 x-mozilla-html:FALSE url:www.actiontarget.com org:Action Target Inc adr:;;PO Box 636;Provo;UT;84603;US version:2.1 email;internet:kyle@actiontarget.com title:President x-mozilla-cpt:;-15520 fn:Kyle Bateman end:vcard
^ permalink raw reply [nested|flat] 7+ messages in thread

* Re: Index not used in functions in 7.0?
@ 2000-05-16 19:11  Tom Lane <tgl@sss.pgh.pa.us>
  parent: Kyle Bateman <kyle@actarg.com>
  1 sibling, 0 replies; 7+ messages in thread

From: Tom Lane @ 2000-05-16 19:11 UTC (permalink / raw)
  To: Kyle Bateman <kyle@actarg.com>; +Cc: pgsql-sql; merrill@actarg.com

Kyle Bateman <kyle@actarg.com> writes:
> create function summtr_ocpt2(text, int4) returns int4 as '
>     select sum(tquant) from mtr_reg where to_proj = $2 and pnum = $1 and
> (status = \'open\' or status = \'clsd\' or status = \'prip\');
>     ' language 'sql';

Ah, I've sussed it.  The difference between the environment in your
function and the environment in a hand-entered query is that you've
declared $1 to be type 'text', whereas in the hand-entered query the
parser resolves the unknown-type string literal into type 'varchar'
to match the type of what it's being compared to.  What you effectively
have inside the function is pnum::text = $1::text, and the planner is
not able to figure out that it can use a varchar index for that.  If you
had written the hand-entered query as "... pnum = '1051'::text ..."
it wouldn't have been done with an indexscan either.

Best short-term solution is to declare the function as taking varchar
in the first place.

This behavior is a regression from 6.5, which handled implicit coercions
differently and could recognize the clause as indexable despite the
inserted coercion.  I'll look into whether it's patchable for 7.0.1.
A proper fix might be too complex to risk patching into 7.0.1 though :-(
... might have to wait for 7.1.

			regards, tom lane



^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: Index not used in functions in 7.0?
@ 2000-05-17 17:18  Peter Eisentraut <peter_e@gmx.net>
  parent: Tom Lane <tgl@sss.pgh.pa.us>
  1 sibling, 0 replies; 7+ messages in thread

From: Peter Eisentraut @ 2000-05-17 17:18 UTC (permalink / raw)
  To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Kyle Bateman <kyle@actarg.com>; pgsql-sql; merrill@actarg.com

Tom Lane writes:

> select attname,attdisbursion,s.*
> from pg_statistic s, pg_attribute a, pg_class c
> where starelid = c.oid and attrelid = c.oid and staattnum = attnum
> and relname = 'FOO';
> 
> (I'm beginning to think we should create a standard system view
> for this query ;-))

\dstats?


-- 
Peter Eisentraut                  Sernanders väg 10:115
peter_e@gmx.net                   75262 Uppsala
http://yi.org/peter-e/            Sweden




^ permalink  raw  reply  [nested|flat] 7+ messages in thread


end of thread, other threads:[~2000-05-17 17:18 UTC | newest]

Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2000-05-16 15:26 Index not used in functions in 7.0? Kyle Bateman <kyle@actarg.com>
2000-05-16 15:50 ` Tom Lane <tgl@sss.pgh.pa.us>
2000-05-16 16:36   ` Tom Lane <tgl@sss.pgh.pa.us>
2000-05-16 18:00     ` mig@utdt.edu
2000-05-16 18:13       ` Kyle Bateman <kyle@actarg.com>
2000-05-17 17:18     ` Peter Eisentraut <peter_e@gmx.net>
2000-05-16 19:11 ` Tom Lane <tgl@sss.pgh.pa.us>

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