From: Andrew Kroeger <andrew@sprocks.gotdns.com>
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:05:41 -0500
Message-ID: <464E0715.7000601@sprocks.gotdns.com> (raw)
In-Reply-To: <A23190A408F7094FAF446C1538222F7603EE4245@avaexch01.avamar.com>
References: <A23190A408F7094FAF446C1538222F7603EE4245@avaexch01.avamar.com>
Tyrrill, Ed wrote:
> mdsdb=# \d backup_location
> Table "public.backup_location"
> Column | Type | Modifiers
> -----------+---------+-----------
> record_id | bigint | not null
> backup_id | integer | not null
> Indexes:
> "backup_location_pkey" PRIMARY KEY, btree (record_id, backup_id)
> "backup_location_rid" btree (record_id)
> Foreign-key constraints:
> "backup_location_bfk" FOREIGN KEY (backup_id) REFERENCES
> backups(backup_id) ON DELETE CASCADE
[snip]
> mdsdb=# explain analyze select record_id from backup_location where
> backup_id = 1070;
>
> QUERY PLAN
>
> ------------------------------------------------------------------------
> ------------------------------------------------------------------------
> -------------
> 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
> (3 rows)
The "backup_location_rid" index on your table is not necessary. The
primary key index on (record_id, backup_id) can be used by Postgres,
even if the query is only constrained by record_id. See
http://www.postgresql.org/docs/8.2/interactive/indexes-multicolumn.html
for details.
The explain plan indicates that your query is filtered on backup_id, but
is using the primary key index on (record_id, backup_id). Based on the
table definition, you do not have any good index for filtering on backup_id.
The explain plan also seems way off, as I would expect a sequential scan
would be used without a good index for backup_id. Did you disable
sequential scans before running this query? Have you altered any other
configuration or planner parameters?
As your "backup_location_rid" is not necessary, I would recommend
dropping that index and creating a new one on just backup_id. This
should be a net wash on space, and the new index should make for a
straight index scan for the query you presented. Don't forget to
analyze after changing the indexes.
Hope this helps.
Andrew
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: andrew@sprocks.gotdns.com, tyrrill_ed@emc.com
Subject: Re: Slow queries on big table
In-Reply-To: <464E0715.7000601@sprocks.gotdns.com>
* 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