one more performance question I cannot test yet :(

On Apr 15, 12:13 am, "Sergei Shelukhin" <realg...@gmail.com> wrote:

Suppose there's a table with two text fields and a lot of int fields.
THe table will be huge, updated constantly, and will be searched by
all of the int fields, whereas text fields will be for display only.

Does it make sense to split it vertically into two tables, one with
ints and one with texts, and join them in queries to make it faster?

Also, with a limited test data I have, I tried running queries against
two table scheme...
ifyou join two tables by id = id ( both ids are primary keys) it uses
hash join, however as soon as you add a condition on one of the
columns in ints' table, it degrades to nested loop. Wtf? Is there any
way to force it to used clustered indexes for gods sake?!

Sergei Shelukhin escribi�:

On Apr 15, 12:13 am, "Sergei Shelukhin" <realg...@gmail.com> wrote:

Suppose there's a table with two text fields and a lot of int fields.
THe table will be huge, updated constantly, and will be searched by
all of the int fields, whereas text fields will be for display only.

Does it make sense to split it vertically into two tables, one with
ints and one with texts, and join them in queries to make it faster?

Also, with a limited test data I have, I tried running queries against
two table scheme...
ifyou join two tables by id = id ( both ids are primary keys) it uses
hash join, however as soon as you add a condition on one of the
columns in ints' table, it degrades to nested loop. Wtf? Is there any
way to force it to used clustered indexes for gods sake?!

No, you cannot force it. What you usually do is make sure it
understands that the indexed access is faster.

Have you run ANALYZE on the tables?

--
Alvaro Herrera http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support