Thread: 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