On Mar 3, 2005, at 6:05 AM, Dracula 007 wrote:
Hello,
I have two "large" tables - "sessions" (about 1.500.000 rows) and "actions" (about 4.000.000 rows), and the "actions" table is connected to the "sessions" (it contains a key from it). The simplified structure of these tables is
sessions ( session_id int4, visitor_id int4, session_ip inet, session_date timestamp )
actions ( action_id int4, session_id int4, -- foreign key, references sessions(session_id) action_date timestamp, action_year int2, action_month int2, action_day int2 )
I run SQL queries like
SELECT COUNT(actions.session_id) AS sessions_count, COUNT(DISTINCT visitor_id) AS visitors_count, COUNT(DISTINCT session_ip) AS ips_count FROM actions LEFT JOIN sessions USING (session_id) GROUP BY action_year, action_month, action_day
but it's really really slow. I've tried to use different indexes on
different columns, but no matter what I've tried I can't get it faster. The explain analyze of the query is
--------------------------------------------------
Aggregate (cost=347276.05..347276.05 rows=1 width=23) (actual time=210060.349..210060.350 rows=1 loops=1)
-> Hash Left Join (cost=59337.55..305075.27 rows=4220077 width=23) (actualtime=24202.338..119924.254 rows=4220077 loops=1)
Hash Cond: ("outer".session_id = "inner".session_id)
-> Seq Scan on actions (cost=0.00..114714.77 rows=4220077 width=8) (actual time=7539.653..44585.023 rows=4220077 loops=1)
-> Hash (cost=47650.64..47650.64 rows=1484764 width=19) (actual time=16628.790..16628.790 rows=0 loops=1)
-> Seq Scan on sessions (cost=0.00..47650.64 rows=1484764 width=19) (actual time=0.041..13378.667 rows=1484764 loops=1)
It looks like you are going to always do a sequential scan on the tables, as you always look a the entire table(s). How often do you do the query as compared to the load on the database? If you do the query often relative to the load, could you keep a table of counts something like:
create table summarize_use ( action_date date, sessions_count int, visitors_count int, isp_count int)
and then use triggers from the sessions and actions to increment the various counts in the summarize_use table based on the action_date and session_date date parts? The summarize_use table would then look like:
action_date sessions_count visitors_count ips_count 3-2-2005 15 12 12
Just a thought, and of course you would pay the price of triggers with each insert to the sessions or actions table.
If you don't like that and you run this say every night at midnight, you could set up a cron job that ran this query and selected it into a table for direct querying--call this table summarize_use again. Then, to get the full result, you would need to simply do something like:
select * from summarize_use union SELECT COUNT(actions.session_id) AS sessions_count, COUNT(DISTINCT visitor_id) AS visitors_count, COUNT(DISTINCT session_ip) AS ips_count FROM actions LEFT JOIN sessions USING (session_id) WHERE action_year=2005 AND action_month=3 AND action_day=3;
This would then be fully up-to-date and would use indices on action_year, action_month, action_day. DISCLAIMER--All of this is untested....
Sean.
---------------------------(end of broadcast)--------------------------- TIP 4: Don't 'kill -9' the postmaster