Re: Valid query times out when run from bash script

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