Re: Help with a subselect inside a view

And about being efficient:

On Thu, Aug 25, 2005 at 08:01:26AM -0700, Bill Moseley wrote:
> DROP VIEW cl;
> CREATE VIEW cl (id, class_time, instructor)
> AS
> SELECT DISTINCT ON(class.id)
> class.id, class.class_time, person.first_name
> FROM class, instructors, person
> WHERE instructors.person = person.id
> AND class.id = instructors.class;

And in a case like above, I'm displaying the list a page at a time.
So I first do a count to find total rows and then a select:

select count(*) from cl where class_time >= now();
select * from cl where class_time >= now() LIMIT 20 OFFSET 40;

I looked at the EXPLAIN ANALYZE for both and both do the join, it
seems. I guess it has to be that way.

So would it be smart to do the initial count on "class" instead
of the view first?

select count(*) from class where class_time >= now();
select * from cl where class_time >= now() LIMIT 20 OFFSET 40;

That is, Postgresql won't figure out that it only need to look at one
table, right?

--
Bill Moseley
moseley(at)hank(dot)org