Date: Tue, 1 Nov 2005 17:23:14 -0600
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?
Message-ID: <20051101232314.GV20349@pervasive.com>
References: <330532b60511011516u3709d826w391a4ac167c6967b@mail.gmail.com>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <330532b60511011516u3709d826w391a4ac167c6967b@mail.gmail.com>
User-Agent: Mutt/1.5.9i

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