From: Tom Lane <tgl@sss.pgh.pa.us>
To: Tyrrill, Ed <tyrrill_ed@emc.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Slow queries on big table
Date: Fri, 18 May 2007 15:59:22 -0400
Message-ID: <1736.1179518362@sss.pgh.pa.us> (raw)
In-Reply-To: <A23190A408F7094FAF446C1538222F7603EE4245@avaexch01.avamar.com>
References: <A23190A408F7094FAF446C1538222F7603EE4245@avaexch01.avamar.com>
"Tyrrill, Ed" <tyrrill_ed@emc.com> writes:
> Index Scan using backup_location_pkey on backup_location
> (cost=0.00..1475268.53 rows=412394 width=8) (actual
> time=3318.057..1196723.915 rows=2752 loops=1)
> Index Cond: (backup_id = 1070)
> Total runtime: 1196725.617 ms
If we take that at face value it says the indexscan is requiring 434
msec per actual row fetched. Which is just not very credible; the worst
case should be about 1 disk seek per row fetched. So there's something
going on that doesn't meet the eye.
What I'm wondering about is whether the table is heavily updated and
seldom vacuumed, leading to lots and lots of dead tuples being fetched
and then rejected (hence they'd not show in the actual-rows count).
The other thing that seems pretty odd is that it's not using a bitmap
scan --- for such a large estimated rowcount I'd have expected a bitmap
scan not a plain indexscan. What do you get from EXPLAIN ANALYZE if
you force a bitmap scan? (Set enable_indexscan off, and enable_seqscan
too if you have to.)
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-performance@postgresql.org
Cc: tgl@sss.pgh.pa.us, tyrrill_ed@emc.com
Subject: Re: Slow queries on big table
In-Reply-To: <1736.1179518362@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