search

Abstrct / Schemaverse

The Schemaverse is a space-based strategy game implemented entirely within a PostgreSQL database. Compete against other players using raw SQL commands to command your fleet. Or, if your PL/pgSQL-foo is strong, wield it to write AI and have your fleet command itself!
schemaverse.com
PostgreSQL License
356 stars 39 forks source link

Performance tuning of in_range_planet() #19

Closed cbbrowne closed 12 years ago

cbbrowne commented 12 years ago

https://github.com/cbbrowne/Schemaverse/commit/747a405b945c278146807c0b764ecd5026b2fc5e

I suggest cherry-picking this, or similar.

  1. There's no reason to be doing a COUNT(*) when checking to see if a ship is in range of a planet.
  2. Shifted to plsql, as that allows the query to be expanded more intelligently by the parser when used rather than it being forcibly hidden inside the function.
cbbrowne commented 12 years ago

Did similar to in_range_ship()

https://github.com/cbbrowne/Schemaverse/commit/4efb9186b18e1e68e4e1eb56cf50686ecb5ac8ac

tigereye commented 12 years ago

Man... Chris.

I don't contribute much to Schemaverse anymore but I read every update that comes in.

I have learned so much about performance optimization by reading your commits and emails.

I know contributing to any open source project is usually a thankless job. I've got a small opportunity to change that here.

Thanks, Chris, for teaching me your secretz. I really do get value from reading your contributions and figured a great gift in return would be a smile and the rosy satisfaction that you're helping random strangers learn more details about Postgres.

Keep on Schemaversing,

Sent from my mobile device

On 2012-04-02, at 18:49, Christopher Browne reply@reply.github.com wrote:

https://github.com/cbbrowne/Schemaverse/commit/747a405b945c278146807c0b764ecd5026b2fc5e

I suggest cherry-picking this, or similar.

  1. There's no reason to be doing a COUNT(*) when checking to see if a ship is in range of a planet.
  2. Shifted to plsql, as that allows the query to be expanded more intelligently by the parser when used rather than it being forcibly hidden inside the function.

Reply to this email directly or view it on GitHub: https://github.com/Abstrct/Schemaverse/issues/19

Abstrct commented 12 years ago

Looks great! I added them both to the master code base. The only thing I changed was the range check. I have some new indexes that these can utilize to speed things up a bit further: CIRCLE(ship.location, ship.range) @> CIRCLE(ship2.location,1)

cbbrowne commented 12 years ago

Glad to be of service!

I wonder if that's part of what got the fleets back to running within the appointed time...