[PERFORM] possible wrong query plan on pg 8.3.5,
Hi, I am running a relativ complex query on pg 8.3.5 and have (possible) wrong query plan. My select : explain analyze select d.ids from a_doc d join a_sklad s on (d.ids=s.ids_doc) join a_nomen n on (n.ids=s.ids_num) join a_nom_gr nmgr on (nmgr.ids=n.ids_grupa) join a_gar_prod_r gr on (gr.ids_a_sklad=s.ids and gr.sernum!='ok') join a_location l on (l.ids=s.ids_sklad) join a_klienti kl on (kl.ids=d.ids_ko) left outer join a_slujiteli sl on (sl.ids=d.ids_slu_ka) left outer join a_slujiteli slu on (slu.ids=d.ids_slu_targ) where d.op=1 AND d.date_op = 12320 AND d.date_op = 12362 and n.num like '191%'; If I run the query without thle last part : and n.num like '191%' it work ok as speed ~ 30 sec on not very big db. If I run the full query it take very long time to go ( i never waited to the end but it take 60 min.) The filed n.num is indexed and looks ok for me. I post explan analyze for query without n.num like '191%' and only explain for query with n.num like '191%' : explain analyze select d.ids from a_doc d join a_sklad s on (d.ids=s.ids_doc) join a_nomen n on (n.ids=s.ids_num) join a_nom_gr nmgr on (nmgr.ids=n.ids_grupa) join a_gar_prod_r gr on (gr.ids_a_sklad=s.ids and gr.sernum!='ok') join a_location l on (l.ids=s.ids_sklad) join a_klienti kl on (kl.ids=d.ids_ko) left outer join a_slujiteli sl on (sl.ids=d.ids_slu_ka) left outer join a_slujiteli slu on (slu.ids=d.ids_slu_targ) where d.op=1 AND d.date_op = 12320 AND d.date_op = 12362 ; - Nested Loop Left Join (cost=345.50..190641.97 rows=1488 width=64) (actual time=446.905..30681.604 rows=636 loops=1) - Nested Loop (cost=345.50..189900.14 rows=1488 width=128) (actual time=446.870..30676.472 rows=636 loops=1) - Nested Loop (cost=345.50..189473.66 rows=1488 width=192) (actual time=427.522..30595.438 rows=636 loops=1) - Nested Loop (cost=345.50..189049.52 rows=1488 width=192) (actual time=370.034..29609.647 rows=636 loops=1) - Hash Join (cost=345.50..178565.42 rows=7204 width=256) (actual time=363.667..29110.776 rows=9900 loops=1) Hash Cond: (s.ids_sklad = l.ids) - Nested Loop (cost=321.79..178442.65 rows=7204 width=320) (actual time=363.163..29096.591 rows=9900 loops=1) - Hash Left Join (cost=321.79..80186.96 rows=4476 width=128) (actual time=278.277..13852.952 rows=8191 loops=1) Hash Cond: (d.ids_slu_ka = sl.ids) - Nested Loop (cost=223.17..80065.83 rows=4476 width=192) (actual time=164.664..13731.739 rows=8191 loops=1) - Bitmap Heap Scan on a_doc d (cost=223.17..36926.67 rows=6598 width=256) (actual time=121.306..587.479 rows=8191 loops=1) Recheck Cond: ((date_op = 12320) AND (date_op = 12362)) Filter: (op = 1) - Bitmap Index Scan on i_doc_date_op (cost=0.00..221.52 rows=10490 width=0) (actual time=107.212..107.212 rows=11265 loops=1) Index Cond: ((date_op = 12320) AND (date_op = 12362)) - Index Scan using a_klienti_pkey on a_klienti kl (cost=0.00..6.53 rows=1 width=64) (actual time=1.598..1.602 rows=1 loops=8191) Index Cond: (kl.ids = d.ids_ko) - Hash (cost=77.72..77.72 rows=1672 width=64) (actual time=113.591..113.591 rows=1672 loops=1) - Seq Scan on a_slujiteli sl (cost=0.00..77.72 rows=1672 width=64) (actual time=10.434..112.508 rows=1672 loops=1) - Index Scan using i_sklad_ids_doc on a_sklad s (cost=0.00..21.90 rows=4 width=256) (actual time=1.582..1.859 rows=1 loops=8191) Index Cond: (s.ids_doc = d.ids) - Hash (cost=19.43..19.43 rows=343 width=64) (actual time=0.460..0.460 rows=343 loops=1) - Seq Scan on a_location l (cost=0.00..19.43 rows=343 width=64) (actual time=0.017..0.248 rows=343 loops=1) - Index Scan using i_a_gar_prod_r_ids_a_sklad on a_gar_prod_r gr (cost=0.00..1.44 rows=1 width=64) (actual time=0.049..0.049 rows=0 loops=9900) Index Cond: (gr.ids_a_sklad = s.ids) Filter: (gr.sernum 'ok'::text) - Index Scan using a_nomen_pkey on a_nomen n (cost=0.00..0.27 rows=1 width=128) (actual time=1.548..1.548 rows=1 loops=636) Index Cond: (n.ids = s.ids_num) - Index Scan
[PERFORM] Persistent Plan Cache
Hi, We have a very large, partitioned, table that we often need to query from new connections, but frequently with similar queries. We have constraint exclusion on to take advantage of the partitioning. This also makes query planning more expensive. As a result, the CPU is fully loaded, all the time, preparing queries, many of which have been prepared, identically, by other connections. Is there any way to have a persistent plan cache that remains between connections? If such a mechanism existed, it would give us a great speedup because the CPU's load for planning would be lightened substantially. Thank you, Joshua Rubin signature.asc Description: OpenPGP digital signature
Re: [PERFORM] Persistent Plan Cache
Joshua Rubin jru...@esoft.com writes: We have a very large, partitioned, table that we often need to query from new connections, but frequently with similar queries. We have constraint exclusion on to take advantage of the partitioning. This also makes query planning more expensive. As a result, the CPU is fully loaded, all the time, preparing queries, many of which have been prepared, identically, by other connections. If you're depending on constraint exclusion, it's hard to see how plan caching could help you at all. The generated plan needs to vary depending on the actual WHERE-clause parameters. regards, tom lane -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] possible wrong query plan on pg 8.3.5,
zz...@mail.bg writes: I am running a relativ complex query on pg 8.3.5 and have (possible) wrong query plan. ... If I run the query without thle last part : and n.num like '191%' it work ok as speed ~ 30 sec on not very big db. If I run the full query it take very long time to go ( i never waited to the end but it take 60 min.) I'm betting that it's badly underestimating the number of rows satisfying the LIKE condition: - Index Scan using i_nomen_num on a_nomen n (cost=0.00..56.39 rows=24 width=128) Index Cond: (((num)::text = '191'::text) AND ((num)::text '192'::text)) Filter: ((num)::text ~~ '191%'::text) Is 24 the right number of rows for that, or anywhere close? If not, try raising the statistics target for this table. regards, tom lane -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Persistent Plan Cache
Tom Lane wrote: Joshua Rubin jru...@esoft.com writes: We have a very large, partitioned, table that we often need to query from new connections, but frequently with similar queries. We have constraint exclusion on to take advantage of the partitioning. This also makes query planning more expensive. As a result, the CPU is fully loaded, all the time, preparing queries, many of which have been prepared, identically, by other connections. If you're depending on constraint exclusion, it's hard to see how plan caching could help you at all. The generated plan needs to vary depending on the actual WHERE-clause parameters. That's what the OP really should've complained about. If we addressed that, so that a generic plan was created that determines which child tables can be excluded at run time, there would be no need for the persistent plan cache. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Persistent Plan Cache
* Heikki Linnakangas (heikki.linnakan...@enterprisedb.com) wrote: That's what the OP really should've complained about. If we addressed that, so that a generic plan was created that determines which child tables can be excluded at run time, there would be no need for the persistent plan cache. This would definitely be nice to have.. I'm not sure what the level of difficulty to do it is though. Stephen signature.asc Description: Digital signature
Re: [PERFORM] [PERFORMANCE] work_mem vs temp files issue
On Aug 19, 2009, at 7:45 PM, Jaime Casanova wrote: AFAIUI, work_mem is used for some operations (sort, hash, etc) for avoiding the use of temp files on disk... In a client server i'm monitoring (pg 8.3.7, 32GB of ram) work_mem is set to 8MB, however i'm seeing a lot of temp files (3 in 4 hours) with small sizes (ie: 2021520 obviously lower than 8MB). so, why? maybe we use work_mem until we find isn't enough and we send just the difference to a temp file? i'm not thinking in raising work_mem until i understand this well, what's the point if we still create temp files that could fit in work_mem... Are you using temp tables? Those end up in pgsql_tmp as well. -- Decibel!, aka Jim C. Nasby, Database Architect deci...@decibel.org Give your computer some brain candy! www.distributed.net Team #1828 -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] [PERFORMANCE] work_mem vs temp files issue
decibel deci...@decibel.org writes: On Aug 19, 2009, at 7:45 PM, Jaime Casanova wrote: AFAIUI, work_mem is used for some operations (sort, hash, etc) for avoiding the use of temp files on disk... In a client server i'm monitoring (pg 8.3.7, 32GB of ram) work_mem is set to 8MB, however i'm seeing a lot of temp files (3 in 4 hours) with small sizes (ie: 2021520 obviously lower than 8MB). so, why? maybe we use work_mem until we find isn't enough and we send just the difference to a temp file? i'm not thinking in raising work_mem until i understand this well, what's the point if we still create temp files that could fit in work_mem... Are you using temp tables? Those end up in pgsql_tmp as well. Uh, no, they don't. It might be useful to turn on trace_sort to see if the small files are coming from sorts. If they're from hashes I'm afraid there's no handy instrumentation ... regards, tom lane -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Persistent Plan Cache
Tom, If you're depending on constraint exclusion, it's hard to see how plan caching could help you at all. The generated plan needs to vary depending on the actual WHERE-clause parameters. Thank you for the reply. We hardcode the parts of the where clause so that the prepared plan will not vary among the possible partitions of the table. The only values that are bound would not affect the planner's choice of table. Thanks, Joshua signature.asc Description: OpenPGP digital signature
Re: [PERFORM] Persistent Plan Cache
Joshua Rubin wrote: We hardcode the parts of the where clause so that the prepared plan will not vary among the possible partitions of the table. The only values that are bound would not affect the planner's choice of table. Then you would benefit from using prepared statements in the client, and/or connection pooling to avoid having to re-prepare because of reconnecting. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
