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@pinpointresearch.com> 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