Skip site navigation (1) Skip section navigation (2)

Re: String Translation

From: David Wheeler <david(at)kineticode(dot)com>
To: sfpug(at)postgresql(dot)org
Subject: Re: String Translation
Date: 2003-10-16 23:37:23
Message-ID: B14EB630-0031-11D8-B1DA-0003931A964A@kineticode.com (view raw or flat)
Thread:
Lists: sfpug
On Thursday, October 16, 2003, at 11:01  AM, Dror Matalon wrote:

> I would try the standard tuning things:
>
> 1. Run VACUUM ANALYZE on the table.
> 2. Run reindex on the table
> 2. Print out the query and then do a explain analyze of the query in
> psql and see how long it takes, and if so why.
>
> If it's still slow, provide the explain analyze here, and we can take a
> look at it.

Looking at it, I realized what the problem is. It's really dumb. Recall 
that the code looked like this:

sub update_all {
     my ($table) = @_;
     my $get_name     = prepare("SELECT name FROM $table");
     my $set_key_name = prepare("UPDATE $table SET key_name=? WHERE 
name=?");

     my $name;
     execute($get_name);
     bind_columns($get_name, \$name);

     while (fetch($get_name)) {
         my $key_name = lc($name);
         $key_name =~ y/a-z0-9/_/cs;
         execute($set_key_name, $key_name, $name);
     }
}

The upshot is that $table may have many rows with the same value in the 
name column! For example, I have over 2200 rows in the table, but when 
I do "SELECT DISTINCT name", there are only 24!! So it was getting all 
2200 rows, and thinking it was changing them one at a time, but it was 
actually changing them over and over again!

So my solution is just to add the DISTINCT.

Naturally, after doing this, the whole thing took only a second to run!

Sorry for sharing this blunder with you all and wasting all of our time!

Regards,

David

-- 
David Wheeler                                     AIM: dwTheory
david(at)kineticode(dot)com                              ICQ: 15726394
http://www.kineticode.com/                     Yahoo!: dew7e
                                                Jabber: Theory(at)jabber(dot)org
Kineticode. Setting knowledge in motion.[sm]


In response to

Responses

sfpug by date

Next:From: Josh BerkusDate: 2003-10-16 23:39:11
Subject: Re: String Translation
Previous:From: eleinDate: 2003-10-16 18:19:35
Subject: Re: String Translation

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group