more postgres tuning in jforum

A teammate installed a new feature on CodeRanch JForum that uses a 4,515,409 row table. When dealing with over a million rows, scans become a huge performance drain. To the point where one query was slow but real usages with many at the same time brought down the app. The reason why the query was slow was interesting so I asked him if I could blog about it.

The original query

select countryCode, countryName, region, city
     from ip2location
     where 540815125 >= low and 540815125 <= high;

Running this through explain says it uses the index with a cost of:

Bitmap Heap Scan on ip2location (cost=5949.66..54170.71 rows=219870 width=32)

That’s a really high cost explain plan.

My first thought was to change it to:

explain  select countryCode, countryName, region, city
     from ip2location
     where 540815125 >= low and 540815125 <= high;

Which has a much better explain plan of

Index Scan using ip2l_low_asc_idx on ip2location (cost=0.00..8.77 rows=1 width=32)

The reason is that in the first query, postgres needs to scan the large index from the beginning until it hits the low value. In the second, I gave it permission to start really close to the target row. I subtracted 1000 but that was arbitrary. It just needs to high enough to be in the vicinity of the row without missing out on any data.

My approach also makes the lookup time consistent. It is always looking through 1000 worth of index. (Which is always less than 1000 rows given the bunching of low to high.) The original is immediate through a full index scan.

Then the original teammate switched it to:

select countryCode, countryName, region, city
     from ip2location
     where  low =
           (select max(low)
            from   ip2location
            where 540815125 >= low)

This has the same explain cost as the hacky one and is clearer.  Same logic though – it doesn’t require scanning/returning extra results.

chromebook on the road

I’ve been testing out a Chromebook at home for a while now.  Friday was the first time I took it “on the road.”  In other words outside my apartment.

Connecting to 3g

At times, 3G connectivity takes a bit longer to start up.  (This probably happens at home too periodically; I just don’t watch startup there.)  Waiting seems to solve it.  Once I had to force it to reconnect by turning off the 3G and then turning it back on.  I also had to click the X to close the message that 3G wasn’t available.

The keyboard

At home, I’ve been using an external keyboard and mouse when doing anything non-trivial.  On the road, I realized I didn’t remember a keyboard shortcut:  alt up/down is paging.

Overall experience

The Chomebook was easy to use.  I was able to:

  1. Check email
  2. Check a website
  3.  Open a word doc
  4. Post on coderanch
  5. Check the address of a restaurant.
On a cell phone you get a dropped call every so often.  On the chromebook, this manifests as a few seconds loss of connectivity.  This happened to be twice. On a google site, you get a nice message that “the application is unreachable.”   On a non-google site, you get a long message about the dns entry not being available.  While this is true, it seems like a scary message for the lay-person.  The solution is to refresh the browser page in either case.
Bandwidth usage
I used the internet for about an hour total on Friday and went through only 4MB.  Granted I didn’t do anything bandwidth heavy, but I still expected it to be higher.

maker faire vs toastmasters – co-running an event

Over the past week and a half, I co-ran two events.  One was the NYC FIRST robotics booth at Maker Faire (see picture.) The other was an area contest for Toastmasters.

Since these two events were so close to each other, I spent some time comparing my experience.

Overview of event

Maker Faire: Norm Sutaria and I have run the NYC FIRST robotics booth at Maker Faire for the past three years.  Our booth is 20 x 30 feet and we coordinate robotics teams from elementary school through high school to show their robots.

Toastmasters contest: Toastmasters holds speech contests where contestants from multiple clubs square off.  Since our area only has four clubs, I ran the contest with another area governor to make it a bigger event.

Ribbon

Our booth won an editors choice award at Maker Faire.  That is really cool and exciting.  Toastmasters gives ribbons out for every speech and certificates of appreciation for helping at a contest.  I don’t find these motivating because you get them frequently regardless of whether you did a good job.  The Maker Faire ribbon actually feels special.  I don’t know how many were given out, but we didn’t win it the last two years making it special for us!

Delegating

For Maker Faire, Norm and I get to practice delegating.  Both to each other – play to the other person’s strengths – and to the teams.  We are both good at delegating to students and not trying to do everything ourselves.  For planning, it was more of claiming the work we wanted to do or were best at rather than delegating.  There was some level of needing to trust the other person at the event though.  It’s important to eat and take breaks knowing the other is in charge!  And for me, remembering to ask for help lifting things when I need to and not worrying about not “pulling my weight” in that specific area.  I do lots of other things!

For Toastmasters, we split planning by event.  I did the lions share of the planning/organizing/running this time and the other area governor will this time.  As a result, less delegating went on.  There was some of course since a contest has a number of contest officials.  And I did seek volunteers for certain parts.

Contingency planning

The first two years, our Maker Faire rain plan was “it better not rain.”  This year, rain was likely so we came up with an actual rain plan.  We announced it both mornings and used it Sunday.  (The plan was to cover up the electronics with tarp and painters plastic, have some students under the tent and encourage most to go into the Hall of Science.)

For Toastmasters, I thought about things likely to go wrong and had an extra judge on hand along with a plan for how to get more if needed.  For the most part, it was “figure out how to run with it” if things went wrong.  Which worked out just fine.  I think this is because a Toastmasters speech contest is a lot more predictable.

Interestingly, this shows a difference between volunteer work and paid work.  In the business world, I don’t typically plan to wing it if something goes wrong!

Google forms

On the tech side, I used Google forms to organize both events.  For attendance lists in both cases and collecting data, listing teams, etc for Maker Faire.

Where I grew the most

Both FIRST robotics and Toastmasters help with soft skills and leadership skills.  Toastmasters does it more obviously – it is in their mission.  FIRST robotics “tricks” students into learning about teamwork and leadership through the “distraction” of a robot.  Does it for the mentors too!  I definitely stretch myself and learn the most at the Maker Faire event.  Partially because I talk to a lot of people I don’t know and give the “FIRST pitch.”  Partially because running the booth is a lot more work.  And partially because a public event is a lot less predictable than a Toastmasters contest.

Both work though.  Because if you tell a techie he/she will spend a weekend practice soft skills, you aren’t likely to get a good reaction.  Throw in robots or the need to get something done and it changes the picture completely.