sql - Optimizing postrgeSQL query with joins and "ghost" references? -

i made query data.

 select th.id, th.ticket_id, tp.name priority, ts.name state, owner_id player_id, g.id team_id, tt.name ticket_type  ticket_history th, ticket_priority tp, ticket_state ts, users, queue, ticket_type tt, groups g  th.priority_id = tp.id  , queue.id = g.id  , th.state_id = ts.id  , owner_id = users.id  , th.queue_id = queue.id  , th.type_id = tt.id  , th.id > $lastupdateid  order th.id desc 

it works fine, got complains query slow (the 'complainer' didn't bother test though)... decided tweak inner joins - first question: efficient way join tables , avoid repeating main id (ticket_history in case) right?

i refactored query this:

 select th.id, th.ticket_id, tp.name priority, ts.name state, owner_id player_id, g.id team_id, tt.name ticket_type  ticket_history, groups  inner join queue on queue.id = groups.id  inner join ticket_priority on ticket_priority.id = ticket_history.priority_id  inner join ticket_state on ticket_state.id = ticket_history.state_id  inner join users on users.id = ticket_history.owner_id  inner join queue on queue.id = ticket_history.queue_id  inner join ticket_type on ticket_type.id = ticket_history.type_id  th.id > 7352616  order th.id desc 

but error

    error:  invalid reference from-clause entry table "ticket_history" line 4: ...ner join ticket_priority on (ticket_priority.id = ticket_his...                                                              ^ hint:  there entry table "ticket_history", cannot referenced part of query. ********** error **********  error: invalid reference from-clause entry table "ticket_history" sql state: 42p01 hint: there entry table "ticket_history", cannot referenced part of query. 

which brings me second question - mean? need reference ticket_history.priority_id ...

ps: goal here data that's in ticket_history, instead of having priority_id want actual priority text , on , forth (except user , group want id's instead of names). trickiest part have queue id in ticket_history , queues table need extract group_id 1 need.

edit: here's example result first query

id;  ticket_id;  priority;  state;  user_id;  group_id;  type     7376618; 203123; "4 low"; "closed"; 385; 22; "service request" 7376617; 201341; "3 medium"; "closed"; 100; 21; "problem" 7376616; 201230; "1 superduperhigh"; "closed"; 150; 10; "service request" 

the query returns 7360 rows in 19067 seconds (give or take) localhost instance. original table "as is" contains id's instead of text/numbers. contains "type_id" instead of type. group_id 1 that's fetches correlation between 2 different tables. goes ticket_history->queue->groups needs relate id's find group_id

edit2: table diagram looks (link) - tables around ticket - it's biggest 1 can see

from documentation:

a join clause combines 2 items, convenience refer "tables", though in reality can type of item. use parentheses if necessary determine order of nesting. in absence of parentheses, joins nest left-to-right. in case join binds more tightly commas separating from-list items.

so successive joins relative "groups", not "ticket_history". flip order of tables make work:

select th.id, th.ticket_id, tp.name priority, ts.name state, owner_id player_id,        g.id team_id, tt.name ticket_type ticket_history th join ticket_priority tp on tp.id = th.priority_id join ticket_state ts on ts.id = th.state_id -- join users on users.id = th.owner_id   not used in query join queue on queue.id = th.queue_id join ticket_type tt on tt.id = th.type_id join groups g on g.id = queue.id th.id > 7352616 order th.id desc; 

note inner before join noise word; can safely omit it.