From: Steinar H. Gunderson <sgunderson@bigfoot.com>
To: Mitch Pirtle <mitch.pirtle@gmail.com>
Cc: pgsql-performance@postgresql.org <pgsql-performance@postgresql.org>
Subject: Re: Joining views disables indexes?
Date: Wed, 2 Nov 2005 00:28:16 +0100
Message-ID: <20051101232816.GA15321@uio.no> (raw)
In-Reply-To: <330532b60511011516u3709d826w391a4ac167c6967b@mail.gmail.com>
References: <330532b60511011516u3709d826w391a4ac167c6967b@mail.gmail.com>
On Tue, Nov 01, 2005 at 06:16:59PM -0500, Mitch Pirtle wrote:
> I have a client that is testing an internal data platform, and they
> were happy with PostgreSQL until they tried to join views - at that
> time they discovered PostgreSQL was not using the indexes, and the
> queries took 24 hours to execute as a result.
>
> Is this a known issue, or is this possibly a site-specific problem?
This is way too general to give a good solution. In general, PostgreSQL
should have no problem using indexes on joins (in versions before 8.0, there
was a problem using indexes on joins of differing data types, though).
This does of course assume that its statistics are good; I assume you've
doing ANALYZE on the database after loading the database?
What you want to do is to post your table definitions and EXPLAIN ANALYZE
output of a slow query; that could be difficult if it takes 24 hours, though,
so you might try a slightly quicker query for starters.
/* Steinar */
--
Homepage: http://www.sesse.net/
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: sgunderson@bigfoot.com, mitch.pirtle@gmail.com
Subject: Re: Joining views disables indexes?
In-Reply-To: <20051101232816.GA15321@uio.no>
* 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