On Jul 16, 2:55 am, "The Devil's Programmer"
<[email protected]> wrote:
> I believe I am supposed to wrap the whole query inside another query
> and put the where clause on the outer query, would this be correct? I
> have tried messing around with this a little but haven't managed to
> get it to work yet. If somebody could just tell me that I'm on the
> right track, that would be great.
First check that the database doesn't figure it out for you. A quick
test on my sample database showed that on postgres this doesn't seem
to be a problem (note the identical query plans):
ants=# EXPLAIN ANALYZE SELECT users.id, (SELECT COUNT(*) FROM
documents WHERE documents.user_id = users.id) AS doc_count FROM users
WHERE (SELECT COUNT(*) FROM documents WHERE documents.user_id =
users.id) > 9;
QUERY
PLAN
-----------------------------------------------------------------------------------------------------------------------------------------
Seq Scan on users (cost=0.00..236010.06 rows=7000 width=4) (actual
time=0.066..190.913 rows=1883 loops=1)
Filter: ((subplan) > 9)
SubPlan
-> Aggregate (cost=8.40..8.41 rows=1 width=0) (actual
time=0.007..0.007 rows=1 loops=21000)
-> Index Scan using user_id_idx on documents
(cost=0.00..8.38 rows=7 width=0) (actual time=0.003..0.005 rows=5
loops=21000)
Index Cond: (user_id = $0)
-> Aggregate (cost=8.40..8.41 rows=1 width=0) (actual
time=0.010..0.010 rows=1 loops=1883)
-> Index Scan using user_id_idx on documents
(cost=0.00..8.38 rows=7 width=0) (actual time=0.003..0.007 rows=10
loops=1883)
Index Cond: (user_id = $0)
Total runtime: 191.296 ms
(10 rows)
ants=# EXPLAIN ANALYZE SELECT * FROM (SELECT users.id, (SELECT COUNT
(*) FROM documents WHERE documents.user_id = users.id) AS doc_count
FROM users) AS x WHERE x.doc_count > 9;
QUERY
PLAN
-----------------------------------------------------------------------------------------------------------------------------------------
Seq Scan on users (cost=0.00..236010.06 rows=7000 width=4) (actual
time=0.064..191.524 rows=1883 loops=1)
Filter: ((subplan) > 9)
SubPlan
-> Aggregate (cost=8.40..8.41 rows=1 width=0) (actual
time=0.007..0.007 rows=1 loops=21000)
-> Index Scan using user_id_idx on documents
(cost=0.00..8.38 rows=7 width=0) (actual time=0.003..0.005 rows=5
loops=21000)
Index Cond: (user_id = $0)
-> Aggregate (cost=8.40..8.41 rows=1 width=0) (actual
time=0.010..0.010 rows=1 loops=1883)
-> Index Scan using user_id_idx on documents
(cost=0.00..8.38 rows=7 width=0) (actual time=0.003..0.007 rows=10
loops=1883)
Index Cond: (user_id = $0)
Total runtime: 191.931 ms
(10 rows)
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to [email protected]
To unsubscribe from this group, send email to
[email protected]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~----------~----~----~----~------~----~------~--~---
