Re: [PERFORM] query runs for more than 24 hours!
On 2017-08-22 16:23, Mariel Cherkassky wrote: SELECT a.inst_prod_id, product_id, nap_area2, nap_phone_num, nap_product_id, b.nap_discount_num, b.nap_makat_cd, nap_act_start_dt, b.nap_debt_line, nap_act_end_dt, b.row_added_dttm b.row_lastmant_dttm, FROM ps_rf_inst_prod a, ANDa.setid || ''= 'SHARE' nap_ip_discount b WHERE nap_crm_status = 'C_04' ANDb.nap_makat_cd IN (SELECT term_codeANDb.setid || ''= 'SHARE' ANDa.inst_prod_id = On my screen the order of the lines in the query seem to get messed up, I'm not sure if that's my email program or a copy/paste error. From what I can see, you are using subselects in an IN statement, which can be a problem if that has to be re-evaluated a lot. It's hard for me to say more because I can't tell what the actual query is at the moment. Regards, Vincent. -- 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] query runs for more than 24 hours!
On 08/22/2017 04:23 PM, Mariel Cherkassky wrote: Hi, I have a query that I run in my postgresql 9.6 database and it runs for more than 24 hours and doesnt finish. My select consist from few joins : I'm sorry, but the query and plans are completely broken (wrapped in funny ways, missing important bits. ...) I don't know what client you use or how that happened, but I recommend attaching the information as text files instead of pasting it into the message directly. Regarding the query analysis - we can't really help you much without seeing an explain analyze (that is, not just the plan and estimates, but actual performance and row counts). That usually identifies the query operations (scans, join, ...) causing issues. Of course, if the query is already running for 24h and you don't know how much longer it will take to complete, running EXPLAIN ANALYZE on it is not very practical. The best thing you can do is break the query into smaller parts and debugging that - start with one table, and then add tables/conditions until the performance gets bad. Hopefully the explain analyze on that will complete in reasonable time. Of course, you haven't told us anything about what's happening on the machine. It is reading a lot of data from the disks? Random or sequential? Is it writing a lot of data into temporary files? Is it consuming a lot of CPU? And so on. regards -- Tomas Vondra http://www.2ndQuadrant.com PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
