All - Thanks for the prompt responses. I've actually narrowed it down a bit and it seems that something earlier (specifically, the for loop that assembles $ADDITIONAL_CREATION_FIELDS and $ADDITIONAL_SELECTION_FIELDS) in the script is causing Bash to write extremely slowly (if I subset the query out itself into an independent Bash script it executes appropriately). Since this is primarily a Bash issue rather than a psql issue I may take my search elsewhere. - D On Tue, Mar 27, 2012 at 1:20 PM, Steve Crawford <scrawford@xxxxxxxxxxxxxxxxxxxx> wrote: > On 03/27/2012 11:37 AM, W. David Jarvis wrote: >> >> Hello all - >> >> I've been trying to get a bash script...but when I run the script the >> SELECT query runs indefinitely. >> >> ... >> >> # Create a bunch of indexes >> echo -n "Creating table indexes for faster querying..." >> psql mta-join<<EOF >> CREATE INDEX campaign_id_index_old ON old_mta (campaign_id); >> ... >> >> CREATE INDEX audience_id_index_new ON old_mta (audience_id); >> ... >> >> psql -d mta-join -t -A -F ',' -c 'copy (SELECT * FROM new_mta, old_mta >> WHERE report_date = report_date_day AND new_mta.campaign_id = >> old_mta.campaign_id AND new_mta.placement_id = old_mta.placement_id >> AND new_mta.creative_id = old_mta.creative_id AND new_mta.package_id = >> old_mta.package_id AND new_mta.audience_id = old_mta.audience_id) to >> stdout;'> $OUTPUT_FILE >> > Some things to try: > > 1. Change "psql" to "echo" and then run the statement that gets dumped out > by hand. See what happens. > > 2. Change your psql command to be an "EXPLAIN...", run the script and > examine the output of the explain. > > Some thoughts: > > 1. You immediately run a select after creating, importing and making a bunch > of indexes. Is it possible that autovacuum hasn't yet analyzed the table and > you are getting a bad query plan? If so (or to make sure it doesn't happen), > update your script to include an ANALYZE of the appropriate tables before > you run the SELECT. > > 2. Run analyze anyway. It is likely that most of the indexes you create are > unused. Unless your data changes so much that the planner would choose > different indexes for different imports, you can probably eliminate the > steps of creating the unused indexes. > > 3. You can probably wrap all the steps into a single connection for a small > speed improvement. Running everything within a single connection would allow > you to use temporary tables which are unlogged. Alternately, since you are > on 9.1, you could leave the script alone and create an unlogged table > "CREATE UNLOGGED TABLE...". Both temporary and unlogged tables don't write > to the WAL so you get a speed improvement in return for the data being at > risk in a crash. In this case, the database is just doing some temporary > processing steps that are deleted anyway so you don't need crash safety. > > Cheers, > Steve > -- W. David Jarvis M: 203.918.2328 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
