In order to explore some postgres performance options with table
collation, I decided to use a little experimental dabase to try out
some of the options I saw. What I want to create queries to combine
data from 2+ tables into individual rows.
So - being a bit of an EQ player, I cobbled together a trivial little
database that tries to generate an 'EQ Jewellery' table. It all works
fine, and it works fine under MS Access or mysql. But under Postgres,
it grinds. It chugs.
When I experimented with the database, I found that it only started to
do this when I go to a fourth level of join.
The database can be found here:
http://www.kfs.org/~oliver/jewellery/dbcreate.sql Definition
http://www.kfs.org/~oliver/jewellery/insert.sql Insert statements
As you'll see - it's a pretty small table.
If you do:
SELECT * FROM jcombo_query WHERE metal_uid = 1 ;
or
SELECT * FROM jcombo_query jq, metal_types mt
WHERE mt.metal_name = 'Silver' AND mt.metal_uid = jq.metal_uid ;
There is no delay.
Also doing
SELECT * from jcombo_query silv, jcombo_query elec
WHERE silv.metal_uid = 1 AND elec.metal_uid = 2 ;
Still no delay. But add a third join
SELECT * from jcombo_query silv, jcombo_query elec, jcombo_query gold
WHERE silv.metal_uid = 1 AND elec.metal_uid = 2 AND gold.metal_uid = 3;
Add a fourth:
SELECT * from jcombo_query silv, jcombo_query elec, jcombo_query gold,
jcombo_query plat
WHERE silv.metal_uid = 1 AND elec.metal_uid = 2 AND gold.metal_uid = 3 AND
plat.metal_uid = 4 ;
And it's painful.
So naturally, when I add the join (stone_types.stone_uid):
SELECT * from jcombo_query silv, jcombo_query elec, jcombo_query gold,
jcombo_query plat, stone_types st
WHERE silv.metal_uid = 1 AND silv.stone_uid = st.stone_uid AND
elec.metal_uid = 2 AND elec.stone_uid = st.stone_uid AND
gold.metal_uid = 3 AND gold.stone_uid = st.stone_uid AND
plat.metal_uid = 4 AND plat.stone_uid = st.stone_uid ;
It takes way way way too long to come back for such a small database.
How can I improve upon this kind of query?
Oliver
--
If at first you don't succeed, skydiving is not for you...
