Trigger function, bad performance - Mailing list pgsql-performance

From Rogatzki Rainer
Subject Trigger function, bad performance
Date
Msg-id 595F977C01388944A4B5158975BB676385ABFD@sgzhmailbox.ggrz-hagen.nrw.de
Whole thread Raw
Responses Re: Trigger function, bad performance  ("Albe Laurenz" <laurenz.albe@wien.gv.at>)
List pgsql-performance
Hello,

I'm having problems with the following bad performing select-statement
in a trigger-function (on update before):

  ...
  for c in
    select id_country, sum(cost) as sum_cost
    from costs
    where id_user = p_id_user
    and id_state = 1
    and date(request) between p_begin and p_until
    group by id_country;
  loop
    ...
  end loop;
  ...

Explain shows that the following existing partial index isn't used:

  CREATE INDEX ix_costs_user_state_date_0701
  ON costs
  USING btree(id_user, id_state, date(request))
  WHERE id_state = 1 AND date(request) >= '2007-01-01'::date AND
date(request) <= '2007-01-31'::date;


The funny thing is, that while executing the statement with type-casted
string-literals the index is used as expected:

  ...
  for c in
    select id_country, sum(cost) as sum_cost
    from costs
    where id_user = p_id_user
    and id_state = 1
    and date(request) between '2007-01-01'::date AND '2007-01-31'::date
    group by id_country;
  loop
    ...
  end loop;
  ...

Any ideas?


Best regards

Rainer Rogatzki (mailto:rainer.rogatzki@ggrz-hagen.nrw.de)

pgsql-performance by date:

Previous
From: Matthew Wakeling
Date:
Subject: Re: Postgres using more memory than it should
Next
From: Franck Routier
Date:
Subject: pg_restore : out of memory