Re: Question about a query plan

Am Freitag, 16. September 2005 16:18 schrieb Bill Moseley:
> First question is why the planner is not using an index scan when I
> use "now()" or CURRENT_TIMESTAMP?
>
>
> EXPLAIN ANALYZE select id from class where class_time > now();
> QUERY PLAN
>
> ---------------------------------------------------------------------------
>------------------------------- Seq Scan on "class" (cost=0.00..655.62
> rows=414 width=4) (actual time=213.327..288.407 rows=28 loops=1) Filter:
> (class_time > now())

The planner thinks your query will return 414 rows, so it thinks the
sequential scan is faster. In reality, your query only retuns 28 rows, so
you need to create better statistics, either by running ANALYZE or VACUUM (or
both) or tweaking the statistics parameters of the columns.

> Perhaps I'm reading that incorrectly, but the sub-select is returning
> 28 rows of "class.id". Then why is it doing a Seq Scan on instructors
> instead of an index scan? If I innumerate all 28 classes I get an
> Index Scan.

Again, bad statistics. It thinks that the scan on instructors is going to
return 1308 rows, which is probably not true.

> Finally, not really a question, but my goal is to show a count of classes
> taught by each in instructor. Perhaps there's a better query?

You could probably rewrite it as a join, but as you could see, the planner
rewrote it as a join internally anyway.

--
Peter Eisentraut
http://developer.postgresql.org/~petere/