How do you rewrite a query in PostgreSQL 8.3
I'm pretty new to the SQL and need some help with a certain task. I have a query that is called by our Flex/Java code that joins multiple tables to get information. Upon running an Explain Analyze, I've seen that the query takes over 15 minutes which sometimes even times out if the site is under heavy traffic. What I am trying to do is to create a rule, or a trigger, that sees if the query is called and selects the data from the materialized view table instead. I tried implementing a rule for this, but was having a hard time when I received errors when trying to join multiple tables in the rule. Basically is there a way, whether it be a rule, trigger, or anything else I have not looked into, for me to see if a certain query gets executed that I can replace that query with one from another table? Thanks in advance.
Example of the 开发者_开发百科query:
select
player_id,
player_names,
player_level,
current_location
from
server_info
join players using (player_id)
join locations using (location_id)
where
current_location = 'Central Hub'
Why not just change the app to source the materialized view if that is what you want? There is no way to rewrite a complex join to source an unrelated table -- that I know of. And, if you could do you really want to have deep voodoo like that in your query planner/rule system? SQL VIEW
s are implemented with a SELECT RULE
, but that doesn't get you from complex-query to materialized view. It gets you from TABLE1, to TABLE2.
A better question would be what are the types on player_id
, and location_id
, are they both int
and do they both have btree
indexes? Is current_location
indexed, and is it being used for equality, regex, or LIKE conditionals? What version of Postgresql are you using? Can you attach an EXPLAIN ANALYZE
of the query that causes the massive wait time?
精彩评论