Обсуждение: Joining views disables indexes?

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.

-- Mitch

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

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/

Mitch Pirtle <mitch.pirtle@gmail.com> writes:
> 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.

You'll need to provide some actual details if you want useful comments.
Let's see the table schemas, the view definitions, and the EXPLAIN plan
(I'll spare you a request for EXPLAIN ANALYZE given that it'd take 24
hours to get ;-) ... although some estimate of the number of rows
expected would be helpful).  And I trust they remembered to ANALYZE the
underlying tables first?  Also, which PG version exactly?

            regards, tom lane