I have two tables, one is called 'users' the other is 'user_activity'.
The 'users' table simply contains the users in the system there is
about 30,000 rows. The 'user_activity' table stores the activities
the user has taken. This table has about 430,000 rows and also
(notably) has a column
On October 28, 2005 2:54 PM
Collin Peters wrote:
I have two tables, one is called 'users' the other is 'user_activity'.
...
I am trying to write a simple query that returns the last time each
user logged into the system. This is how the query looks at the
moment:
SELECT u.user_id,
These two queries execute at exactly the same speed. When I run run
EXPLAIN on them both they return the *exact* same query plan as well.
I find this strange... but it is also kind of what I expected from
reading up on various things. I am under the impression the
postgresql will break up your
A little bit more on my last post that I forget to mention. The two
queries run at the same speed and have the same plan only if I have an
index on the user_activity.user_id column. Otherwise they run at
different speeds. The query you gave me actually runs slower without
the index. All this
On Fri, Oct 28, 2005 at 03:40:40PM -0700, Roger Hand wrote:
You're first joining against the entire user table, then filtering out the
users
you don't need.
That's just wrong, sorry -- the planner is perfectly able to push the WHERE
down before the join.
I'd guess the problem is the age()
Postgres is somewhat speed-challenged on aggregate functions.
The most-repeated work-around would be something like:
SELECT u.user_id,
(SELECT activity_date
FROM user_activity
WHERE user_activity.user_id = pp_users.user_id
AND user_activity_type_id = 7
ORDER BY activity_date DESC
LIMIT
