Re: planner issue with constraint exclusion

Hello,

I ran into this problem recently:

https://projects.commandprompt.com/public/replicator/pastebin?show=f1288d4d8%0D

Of the functions the only one that will use constraint_exclusion is the
one that explicitly passes the date value. I kind of get why except for
the one that uses EXECUTE. As EXECUTE has to replan the query, shouldn't
it be able to use constraint_exclusion?

(text also below for those that don't want to fire up a browser)

CREATE OR REPLACE FUNCTION test_search1() RETURNS integer AS $$
DECLARE
temp date;
tmp integer;
BEGIN
SELECT date(timehit) INTO temp FROM foo51 WHERE unit_id = 1 LIMIT 1;
SELECT l.unit_id FROM INTO tmp foo_stats_day lsd, foo51 l WHERE lsd.a_serv_id = l.unit_id AND lsd.a_serv_id = 1 AND day = date(timehit);
RETURN tmp;
END
$$ LANGUAGE plpgsql STABLE;

CREATE OR REPLACE FUNCTION test_search2() RETURNS integer AS $$
DECLARE
temp date;
tmp integer;
BEGIN
SELECT date(timehit) INTO temp FROM foo51 WHERE unit_id = 1 LIMIT 1;
SELECT l.unit_id FROM INTO tmp foo_stats_day lsd, foo51 l WHERE lsd.a_serv_id = l.unit_id AND lsd.a_serv_id = 1 AND day = '2006-07-17';
RETURN tmp;
END
$$ LANGUAGE plpgsql STABLE;

CREATE OR REPLACE FUNCTION test_search3() RETURNS integer AS $$
DECLARE
temp date;
tmp integer;
BEGIN
SELECT date(timehit) INTO temp FROM foo51 WHERE unit_id = 1 LIMIT 1;
SELECT l.unit_id FROM INTO tmp foo_stats_day lsd, foo51 l WHERE lsd.a_serv_id = l.unit_id AND lsd.a_serv_id = 1 AND day = temp;
RETURN tmp;
END
$$ LANGUAGE plpgsql STABLE;

CREATE OR REPLACE FUNCTION test_search4() RETURNS integer AS $$
use strict;
use warnings;
my $sql = "SELECT date(timehit) AS timehit FROM foo51 WHERE unit_id = 1 LIMIT 1";
my $rv = spi_exec_query($sql);
return undef if( ! defined $rv->{rows}[0]->{'timehit'} );
my $date = $rv->{rows}[0]->{'timehit'};

$sql = "SELECT l.unit_id FROM foo_stats_day lsd, foo51 l WHERE lsd.a_serv_id = l.unit_id AND lsd.a_serv_id = 1 AND day = '$date'";
$rv = spi_exec_query($sql);

return undef if( ! defined $rv->{rows}[0]->{'unit_id'} );
my $unit_id = $rv->{rows}[0]->{'unit_id'};

return $unit_id;
$$ LANGUAGE 'plperlu' STABLE;

CREATE OR REPLACE FUNCTION test_search5() RETURNS integer AS $$
DECLARE
temp date;
tmp integer;
BEGIN
SELECT timehit INTO temp FROM foo51 WHERE unit_id = 1 LIMIT 1;
EXECUTE 'SELECT l.unit_id FROM foo_stats_day lsd, foo51 l WHERE lsd.a_serv_id = l.unit_id AND lsd.a_serv_id = 1 AND day = ''' || temp || '''';
RETURN 1;
END
$$ LANGUAGE plpgsql STABLE;

--
PostgreSQL
Consulting, Development, Support, Training
503-667-4564 - http://www.commandprompt.com/
The PostgreSQL Company, serving since 1997

"Joshua D. Drake" <jd(at)commandprompt(dot)com> writes:
> Of the functions the only one that will use constraint_exclusion is the
> one that explicitly passes the date value.

Since you haven't shown us the constraints you're talking about, or the
resulting plans, it's difficult for anyone to guess what's going on.

regards, tom lane

On Mon, 2008-12-15 at 14:28 -0500, Tom Lane wrote:
> "Joshua D. Drake" <jd(at)commandprompt(dot)com> writes:
> > Of the functions the only one that will use constraint_exclusion is the
> > one that explicitly passes the date value.
>
> Since you haven't shown us the constraints you're talking about, or the
> resulting plans, it's difficult for anyone to guess what's going on.

Table "public.foo_stats_day"
Column | Type | Modifiers
------------------+---------+-----------
a_serv_id | integer | not null
day | date | not null
num_leads | integer |
num_subscribed | integer |
num_unsubscribed | integer |
num_unverified | integer |
Indexes:
"foo_stats_day_pkey" PRIMARY KEY, btree (a_serv_id, day)
Triggers:
partion_public_foo_stats_day_trigger BEFORE INSERT OR UPDATE ON
lead_stats_day FOR EACH ROW EXECUTE PROCEDURE
partion_public_foo_stats_day()

app=# \d year_2007.foo_stats_day_q1
Table "year_2007.foo_stats_day_q1"
Column | Type | Modifiers
------------------+---------+-----------
a_serv_id | integer | not null
day | date | not null
num_leads | integer |
num_subscribed | integer |
num_unsubscribed | integer |
num_unverified | integer |
Indexes:
"foo_stats_day_pkey_q1" PRIMARY KEY, btree (a_serv_id, day),
tablespace "year_2007_indexes"
Check constraints:
"foo_stats_day_q1_day_check" CHECK (day >= '2007-01-01'::date AND
day < '2007-04-01'::date)
Inherits: foo_stats_day
Tablespace: "year_2007"

I am not sure which plans I should show you as they are function plans
thereby not very useful.

Sincerely,

Joshua D. Drake

>
> regards, tom lane
>
--
PostgreSQL
Consulting, Development, Support, Training
503-667-4564 - http://www.commandprompt.com/
The PostgreSQL Company, serving since 1997

* Joshua D. Drake (jd(at)commandprompt(dot)com) wrote:
> On Mon, 2008-12-15 at 14:28 -0500, Tom Lane wrote:
> > "Joshua D. Drake" <jd(at)commandprompt(dot)com> writes:
> > > Of the functions the only one that will use constraint_exclusion is the
> > > one that explicitly passes the date value.
> >
> > Since you haven't shown us the constraints you're talking about, or the
> > resulting plans, it's difficult for anyone to guess what's going on.
>
> I am not sure which plans I should show you as they are function plans
> thereby not very useful.

Ideally, do a 'raise notice' with the exact string you're passing to
EXECUTE, and then run an explain on that. Constraint Exclusion
certainly does work when using explain and constants in general. Have
you tried adding an explicit cast, ala:

EXECUTE 'SELECT l.unit_id FROM foo_stats_day lsd, foo51 l WHERE lsd.a_serv_id = l.unit_id AND lsd.a_serv_id = 1 AND day = ''' || temp || '''::date';

Enjoy,

Stephen

"Joshua D. Drake" <jd(at)commandprompt(dot)com> writes:
> On Mon, 2008-12-15 at 14:28 -0500, Tom Lane wrote:
>> "Joshua D. Drake" <jd(at)commandprompt(dot)com> writes:
>>> Of the functions the only one that will use constraint_exclusion is the
>>> one that explicitly passes the date value.
>>
>> Since you haven't shown us the constraints you're talking about, or the
>> resulting plans, it's difficult for anyone to guess what's going on.

> Check constraints:
> "foo_stats_day_q1_day_check" CHECK (day >= '2007-01-01'::date AND
> day < '2007-04-01'::date)

Well, that certainly looks like constraint exclusion should work with
the EXECUTE case. What leads you to conclude it doesn't?

regards, tom lane