PostgreSQL Query Speed Issues
От | Joseph Pravato |
---|---|
Тема | PostgreSQL Query Speed Issues |
Дата | |
Msg-id | 9678018.67.1361479743893.JavaMail.Joey@Goku обсуждение исходный текст |
Ответы |
Re: PostgreSQL Query Speed Issues
|
Список | pgsql-novice |
Hi! We are running into an interesting issue with PostgreSQL 9.2. Some of the queries are returning in 5-30 minutes where othersnever return at all. The same queries return in several seconds in other databases we have tested on with the same data. Here is a sample querythat we are running into this issue with, hopefully some insight on this issue will help us solve a similar problemwith the rest of the queries. NOTE: All database tests were done without changing or updating any settings after install. # rows in contact: 574939 # rows in contact_address_map: 574924 Original: NEVER RETURNS (tested to over an hour) select * from contact where id not in (select contact_id from contact_address_map) Result from an 'explain analyze': Since the original query never returned, this was not run. Optimized for Postgres: RETURNS select c.* from contact c left join CONTACT_ADDRESS_MAP cam on cam.CONTACT_ID = c.id where cam.ADDRESS_ID is null Result from an 'explain analyze': QUERY PLAN Merge Left Join (cost=83512.87..158059.12 rows=1 width=952) (actual time=5224.171..5224.270 rows=15 loops=1) Merge Cond: (c.id = cam.contact_id) Filter: (cam.address_id IS NULL) Rows Removed by Filter: 574924 -> Index Scan using contact_pkey on contact c (cost=0.00..63048.48 rows=574917 width=952) (actual time=0.009..852.708rows=574939 loops=1) -> Materialize (cost=83512.59..86387.17 rows=574917 width=16) (actual time=1407.387..3107.246 rows=574924 loops=1) -> Sort (cost=83512.59..84949.88 rows=574917 width=16) (actual time=1407.379..2027.946 rows=574924 loops=1) Sort Key: cam.contact_id Sort Method: external sort Disk: 14616kB -> Seq Scan on contact_address_map cam (cost=0.00..8857.17 rows=574917 width=16) (actual time=0.018..578.348rows=574924 loops=1) Total runtime: 5228.459 ms We have done some investigation online and it looks like many of these speed issues require hand optimizing each query specificto PostgreSQL where as other databases seem to do it automatically. Is it possible to change a configuration optionon the database or does PostgreSQL require hand optimization to all queries to run optimally. Any help would be greatly appreciated.
В списке pgsql-novice по дате отправления: