Re: Index scan never executed?

On Thu, 29 May 2003, Chad Thompson wrote:

> I have never been very good at reading these query plans, but I have a bit
> of a problem w/ my query. So any help is appreciated.
>
> The query is fairly self explanitory i think. 2 tables, call_results ( 6.5
> Million records ) and lists ( 11 Million records )
>
> weblink=# explain analyze
> weblink-# select count(*) as count
> weblink-# from call_results cr join lists l on cr.list_id = l.id
> weblink-# where cr.project_id = '55'
> weblink-# and cr.start_time between '4/4/2003 0:0' and now()
> weblink-# and l.list_of_lists_id = '691';
>
>
> QUERY PLAN
> ----------------------------------------------------------------------------
> ----
> -------------------------------------------------------------------------
> Aggregate (cost=2519.58..2519.58 rows=1 width=16) (actual
> time=110715.45..110715.46 rows=1 loops=1)
> -> Nested Loop (cost=0.00..2519.58 rows=1 width=16) (actual
> time=110715.43..110715.43 rows=0 loops=1)
> -> Index Scan using start_time_idx on call_results cr
> (cost=0.00..2021.00 rows=164 width=8) (actual time=110715.42..110715.42
> rows=0 loops=1)
> Index Cond: ((start_time >= '2003-04-04
> 00:00:00-07'::timestamp with time zone) AND (start_time <= now()))
> Filter: (project_id = 55::bigint)
> -> Index Scan using lists_pkey on lists l (cost=0.00..3.03 rows=1
> width=8) (never executed)
> Index Cond: ("outer".list_id = l.id)
> Filter: (list_of_lists_id = 691::bigint)
> Total runtime: 110747.58 msec
> (9 rows)
>
> The big thing I dont understand is why it tells me (never executed) on
> lists_pkey.

It's doing a nested loop. AFAIK, it's saying for each row returned from
the index scan on call_results it does a scan on lists. Since no rows
were returned from call_results it didn't need to ever scan lists.

I think you want an index on (start_time, project_id). It looks like
there are lots of rows matching start_time conditions but none of those
have project_id 55, so you'd be doing alot of extra disk access.