Thread: Select retrieval slowdown after db drop/reload. Suggestions?
I have a select statement, used in a Perl program, which is supposed to find all records related to those in one table which have a delete_dt field value of four years or older.
This is the select statement:
SELECT t2.dist_id, t1.clnt_seq, t2.cntrct_seq, t2.cntrct_id, t3.aunit_seq, t1.person_seq, t1.addr_seq, t3.addr_seq, t4.frst_nm || ' ' || t4.lst_nm AS name, t5.addr_1, t6.acntdel_dt FROM t1, t2, t3, t4, t5, t6 WHERE t1.clnt_seq = t2.clnt_seq AND t2.cntrct_seq = t3.cntrct_seq AND t3.aunit_seq = t6.aunit_seq AND t1.person_seq = t4.person_seq AND t3.addr_seq = t5.addr_seq AND t1.active_flg =0 AND t2.active_flg =0 AND t3.active_flg = 0 AND t6.acntdel_dt < now() - '4 years'::interval order by t2.cntrct_id asc;
SELECT t2.dist_id, t1.clnt_seq, t2.cntrct_seq, t2.cntrct_id, t3.aunit_seq, t1.person_seq, t1.addr_seq, t3.addr_seq, t4.frst_nm || ' ' || t4.lst_nm AS name, t5.addr_1, t6.acntdel_dt FROM t1, t2, t3, t4, t5, t6 WHERE t1.clnt_seq = t2.clnt_seq AND t2.cntrct_seq = t3.cntrct_seq AND t3.aunit_seq = t6.aunit_seq AND t1.person_seq = t4.person_seq AND t3.addr_seq = t5.addr_seq AND t1.active_flg =0 AND t2.active_flg =0 AND t3.active_flg = 0 AND t6.acntdel_dt < now() - '4 years'::interval order by t2.cntrct_id asc;
I'm working on a test box at the moment; the db I am using was made by dumping the production db and copying it over to the test box to be loaded into a newly-created db there. It took a while for me to get the original Perl program working, as I don't really understand Perl, but after I did so, I dropped the db and reloaded it again off of the original files, so I could try another test run and pay attention to what's happening.
On the original load of the test db, the query above had a run time of roughly 3, 3.5 minutes before giving results. Considering the size of the db it's searching through, I feel that's fairly reasonable, especially since that's about what the production db does on the same query. Now, after the drop/recreate, the test db is taking somewhat longer to give back its results; just shy of 7 minutes, if I've done the math correctly. (Timing results - Time: 417531.436 ms)
I'm the only person working on this particular box at this point. This problem did not start until I reloaded the db from the original files. Obviously, I managed to miss something in the drop/reload process, but I have no clue what. I'm running a vacuum full analyze at the moment; if anyone has any other suggestions as to what I could do to solve this (admittedly minor) problem, I would be grateful to hear them.
Thank you for your consideration.
No need to miss a message. Get email on-the-go
with Yahoo! Mail for Mobile. Get started.
On Wed, 2007-02-28 at 13:55, Andrew Edson wrote: > I have a select statement, used in a Perl program, which is supposed > to find all records related to those in one table which have a > delete_dt field value of four years or older. > > This is the select statement: > SELECT t2.dist_id, t1.clnt_seq, t2.cntrct_seq, t2.cntrct_id, > t3.aunit_seq, t1.person_seq, t1.addr_seq, t3.addr_seq, t4.frst_nm || ' > ' || t4.lst_nm AS name, t5.addr_1, t6.acntdel_dt FROM t1, t2, t3, t4, > t5, t6 WHERE t1.clnt_seq = t2.clnt_seq AND t2.cntrct_seq = > t3.cntrct_seq AND t3.aunit_seq = t6.aunit_seq AND t1.person_seq = > t4.person_seq AND t3.addr_seq = t5.addr_seq AND t1.active_flg =0 AND > t2.active_flg =0 AND t3.active_flg = 0 AND t6.acntdel_dt < now() - '4 > years'::interval order by t2.cntrct_id asc; What version of pgsql are you running? Is acntdel_dt indexed? What does explain (and maybe explain analyze) for this query say? What does it say on your other boxes?
You have to run ANALYZE; on your db after a drop/reload to recollect the stats. In the rest db, jus run ANALYZE; and then see how fast it is. I'd guess that this is your issue. Regards, - Naz. Andrew Edson wrote: <blockquote cite="mid984673.72139.qm@web34205.mail.mud.yahoo.com" type="cite"> I have a select statement, used in a Perl program, which is supposed to find all records related to those in one table which have a delete_dt field value of four years or older. This is the select statement: SELECT t2.dist_id, t1.clnt_seq, t2.cntrct_seq, t2.cntrct_id, t3.aunit_seq, t1.person_seq, t1.addr_seq, t3.addr_seq, t4.frst_nm || ' ' || t4.lst_nm AS name, t5.addr_1, t6.acntdel_dt FROM t1, t2, t3, t4, t5, t6 WHERE t1.clnt_seq = t2.clnt_seq AND t2.cntrct_seq = t3.cntrct_seq AND t3.aunit_seq = t6.aunit_seq AND t1.person_seq = t4.person_seq AND t3.addr_seq = t5.addr_seq AND t1.active_flg =0 AND t2.active_flg =0 AND t3.active_flg = 0 AND t6.acntdel_dt < now() - '4 years'::interval order by t2.cntrct_id asc; I'm working on a test box at the moment; the db I am using was made by dumping the production db and copying it over to the test box to be loaded into a newly-created db there. It took a while for me to get the original Perl program working, as I don't really understand Perl, but after I did so, I dropped the db and reloaded it again off of the original files, so I could try another test run and pay attention to what's happening. On the original load of the test db, the query above had a run time of roughly 3, 3.5 minutes before giving results. Considering the size of the db it's searching through, I feel that's fairly reasonable, especially since that's about what the production db does on the same query. Now, after the drop/recreate, the test db is taking somewhat longer to give back its results; just shy of 7 minutes, if I've done the math correctly. (Timing results - Time: 417531.436 ms) I'm the only person working on this particular box at this point. This problem did not start until I reloaded the db from the original files. Obviously, I managed to miss something in the drop/reload process, but I have no clue what. I'm running a vacuum full analyze at the moment; if anyone has any other suggestions as to what I could do to solve this (admittedly minor) problem, I would be grateful to hear them. Thank you for your consideration. No need to miss a message. <a href="http://us.rd.yahoo.com/evt=43910/*http://mobile.yahoo.com/mail">Get email on-the-go with Yahoo! Mail for Mobile. <a href="http://us.rd.yahoo.com/evt=43910/*http://mobile.yahoo.com/mail">Get started.