Re: out of memory - Mailing list pgsql-performance

Hello

I think this SQL returns the following  error.

ERROR:  missing FROM-clause entry for table "email_track"
LINE 3: email_track.count AS "Emails_Access_Count",
         ^

For a fact ,this SQL does not have the "email_trac" table  in from-clause.

1)Is this SQL right?
2)If the SQL is right, can you write how make your table?
   I'd like to try your SQL.
3)What version do you use?

This is my test.

================================================================
--PostgreSQL8.4.4. for CentOS5.3
begin;

create schema email_track;
create table email_track.crmentity
(crmid int,
deleted int,
createdtime date);

create table email_track.activity
(activityid int,
activitytype varchar(100),
date_start date,
subject varchar(100));


create table email_track.emaildetails
(emailid int
);

create table email_track.vantage_email_track
(vantage_email_track int,
mailid int);

create table email_track.seactivityrel
(activityid int);

set search_path to email_track,public;



explain analyze
select
email_track.count AS "Emails_Access_Count",
activity.subject AS "Emails_Subject",
crmentity.crmid AS EntityId_crmentitycrmid
from
(select * from crmentity where deleted = 0 and createdtime between
(now() - interval '6 month') and now() ) as crmentity
inner join
(select * from activity where  activitytype = 'Emails' and date_start
between (now() - interval '6 month')  and now()) as activity
on crmentity.crmid=activity.activityid
inner join emaildetails on emaildetails.emailid = crmentity.crmid
inner join vantage_email_track on
vantage_email_track.mailid=emaildetails.emailid
left join seactivityrel on seactivityrel.activityid = emaildetails.emailid;


ERROR:  missing FROM-clause entry for table "email_track" at character 24
STATEMENT:  explain analyze
         select
         email_track.count AS "Emails_Access_Count",
         activity.subject AS "Emails_Subject",
         crmentity.crmid AS EntityId_crmentitycrmid
         from
         (select * from crmentity where deleted = 0 and createdtime
between (now() - interval '6 month') and now() ) as crmentity
         inner join
         (select * from activity where  activitytype = 'Emails' and
date_start between (now() - interval '6 month')  and now()) as activity
         on crmentity.crmid=activity.activityid
         inner join emaildetails on emaildetails.emailid = crmentity.crmid
         inner join vantage_email_track on
vantage_email_track.mailid=emaildetails.emailid
         left join seactivityrel on seactivityrel.activityid =
emaildetails.emailid;
ERROR:  missing FROM-clause entry for table "email_track"
LINE 3: email_track.count AS "Emails_Access_Count",
         ^

--can not reproduce.
abort;

================================================================

(2010/06/13 22:25), AI Rumman wrote:
> Whenever I run this query, I get out of memory error:
>
>
> explain analyze
> *select *
> email_track.count AS "Emails_Access_Count",
> activity.subject AS "Emails_Subject",
> crmentity.crmid AS EntityId_crmentitycrmid
> *from *
> (select * from crmentity where deleted = 0 and createdtime between
> (now() - interval '6 month') and now() ) as crmentity
> inner join
> (select * from activity where  activitytype = 'Emails' and date_start
> between (now() - interval '6 month')  and now()) as activity
> on crmentity.crmid=activity.activityid
> inner join emaildetails on emaildetails.emailid = crmentity.crmid
> inner join vantage_email_track on
> vantage_email_track.mailid=emaildetails.emailid
> left join seactivityrel on seactivityrel.activityid = emaildetails.emailid
>