From: Tom Lane <tgl@sss.pgh.pa.us>
To: Chad Thompson <chad@weblinkservices.com>
Cc: pgsql-sql <pgsql-sql@postgresql.org>
Subject: Re: Index scan never executed?
Date: Thu, 29 May 2003 14:46:34 -0400
Message-ID: <28951.1054233994@sss.pgh.pa.us> (raw)
In-Reply-To: <004601c32606$884af960$32021aac@chad>
References: <004601c32606$884af960$32021aac@chad>
"Chad Thompson" <chad@weblinkservices.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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: tgl@sss.pgh.pa.us, chad@weblinkservices.com
Subject: Re: Index scan never executed?
In-Reply-To: <28951.1054233994@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox