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@hank.org