The performance will be, at best case, O(N*M^2) where N is the number of rows in chat and M is the number of rows in post. It will probably degrade a lot faster than that. We recently discussed that in another thread.
You appear to be trying to find the "earliest post" for each "thread". Note the following comments:
- the chat table is useless when querying data as it does nothing except waste time and you can tell this because
(a) there is nothing from that table in the select list; and,
(b) removing all reference to it does not affect the query result in any way.
- (chatid, date) must be a candidate key for the post table (that is, it must be unique). If it is not unique then the concept of top and bottom of a chatid cannot use that field and some other tiebreaker must be used. If some other tiebreaker must be used, just use that directly and forget about the added complication of adding something useless.
So, you want the following:
```
create table chat
(
id integer primary key
);
create table post
(
id integer primary key,
chatid integer not null references chat(id),
date text,
unique (chatid, date)
);
with mins (chatid, mindate)
as (
select chatid,
min(date)
from post
group by chatid
)
select post.*
from mins, post
where mins.chatid == post.chatid
and mins.mindate == post.date;
```