From: Jim C. Nasby <jnasby@pervasive.com>
To: Mitch Pirtle <mitch.pirtle@gmail.com>
Cc: pgsql-performance@postgresql.org <pgsql-performance@postgresql.org>
Subject: Re: Joining views disables indexes?
Date: Tue, 1 Nov 2005 17:23:14 -0600
Message-ID: <20051101232314.GV20349@pervasive.com> (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?
>
> They just implemented the exact same datamodel in MySQL 5.0, with
> views and InnoDB tables, and performance is still subsecond.
>
> Would love to know if this is a known issue.
Views simply get expanded to a full query, so the views have nothing to
do with it.
Make sure that they've run analyze on the entire database. Upping
default_statistics_target to 100 is probably a good idea as well.
If that doesn't work, get an explain analyze of the query and post it
here. You can try posting just an explain, but that's much less useful.
--
Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
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: jnasby@pervasive.com, mitch.pirtle@gmail.com
Subject: Re: Joining views disables indexes?
In-Reply-To: <20051101232314.GV20349@pervasive.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