Re: Index scan never executed?

"Chad Thompson" <chad(at)weblinkservices(dot)com> writes:
> 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.

Because it was never executed --- the outer scan on call_results
produced zero rows, so there was never a reason to run the inner scan.
A nestloop join scans the inner table once for each row output by the
outer table.

> I also dont see where all the time is being taken up. I thought that
> (actual time=110715.42..110715.42) meant from millisecond this... TO
> millisecond that, but that would mean that this index scan took no time.

No, the first "actual time" number means the time required to produce
the first output row. So the call_results scan is what is taking all
the time (as you'd expect, given that there's nothing for any of the
other steps to do).

I guess it's a little unclear what to print for the first number when no
rows are output at all. The code evidently is using the total time spent
in the plan node, but I think it would be at least as justifiable to
print a zero instead. Would you have found that less confusing? Anyone
else have an opinion about whether to change that detail?

> TIA for any suggestions on how to make this query faster.

Doesn't seem like the index on start_time is doing you much good ---
you're obviously scanning a huge lot of rows and not finding anything.
Perhaps an index on project_id would be more helpful, or a two-column
index on (project_id, start_time).

regards, tom lane