Alvaro Herrera wrote:
Madison Kelly wrote:
Thanks for your reply!
Unfortunately, in both cases I get the error:
nmc=> SELECT dom_id, dom_name, COUNT(usr_dom_id) AS usr_count FROM domains
JOIN users ON (usr_dom_id=dom_id) HAVING COUNT (usr_dom_id) > 0 ORDER BY
dom_name;
ERROR: syntax error at or near "COUNT" at character 25
LINE 1: SELECT dom_id, dom_name COUNT(usr_dom_id) AS usr_count FROM ...
Try to avoid missing the comma before the COUNT (and do not cheat when
cut'n pasting ...)
Also it seems you will need a GROUP BY clause:
GROUP BY dom_id, dom_name
(placed just before the HAVING clause).
Bingo!
Now to answer the performance questions (using my actual queries,
unedited so they are a little longer):
-=-=-=-=-=-
nmc=> EXPLAIN ANALYZE SELECT dom_id, dom_name, dom_note,
COUNT(usr_dom_id) AS usr_count FROM domains JOIN users ON
(usr_dom_id=dom_id) GROUP BY dom_id, dom_name, dom_note HAVING COUNT
(usr_dom_id) > 0 ORDER BY dom_name;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------
Sort (cost=10.70..10.78 rows=31 width=72) (actual time=2.107..2.133
rows=17 loops=1)
Sort Key: domains.dom_name
-> HashAggregate (cost=9.39..9.93 rows=31 width=72) (actual
time=1.899..1.956 rows=17 loops=1)
Filter: (count(usr_dom_id) > 0)
-> Hash Join (cost=7.20..9.00 rows=31 width=72) (actual
time=0.942..1.411 rows=96 loops=1)
Hash Cond: ("outer".dom_id = "inner".usr_dom_id)
-> Seq Scan on domains (cost=0.00..1.31 rows=31
width=68) (actual time=0.227..0.321 rows=31 loops=1)
-> Hash (cost=6.96..6.96 rows=96 width=4) (actual
time=0.673..0.673 rows=96 loops=1)
-> Seq Scan on users (cost=0.00..6.96 rows=96
width=4) (actual time=0.010..0.371 rows=96 loops=1)
Total runtime: 2.454 ms
(10 rows)
-=-=-=-=-=-
Versus:
-=-=-=-=-=-
nmc=> EXPLAIN ANALYZE SELECT d.dom_id, d.dom_name, d.dom_note, (SELECT
COUNT(*) FROM users u WHERE u.usr_dom_id=d.dom_id) AS usr_count FROM
domains d WHERE (SELECT COUNT(*) FROM users u WHERE
u.usr_dom_id=d.dom_id) > 0 ORDER BY d.dom_name ASC;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------
Sort (cost=297.37..297.39 rows=10 width=68) (actual
time=10.171..10.196 rows=17 loops=1)
Sort Key: dom_name
-> Seq Scan on domains d (cost=0.00..297.20 rows=10 width=68)
(actual time=0.508..10.013 rows=17 loops=1)
Filter: ((subplan) > 0)
SubPlan
-> Aggregate (cost=7.21..7.21 rows=1 width=0) (actual
time=0.203..0.204 rows=1 loops=31)
-> Seq Scan on users u (cost=0.00..7.20 rows=1
width=0) (actual time=0.127..0.189 rows=3 loops=31)
Filter: (usr_dom_id = $0)
-> Aggregate (cost=7.21..7.21 rows=1 width=0) (actual
time=0.184..0.186 rows=1 loops=17)
-> Seq Scan on users u (cost=0.00..7.20 rows=1
width=0) (actual time=0.058..0.164 rows=6 loops=17)
Filter: (usr_dom_id = $0)
Total runtime: 10.593 ms
(12 rows)
-=-=-=-=-=-
So using the JOIN you all helped me with, the query returns in 2.454
ms compared to my early query of 10.593 ms!
I have not yet looked into any indexing either. I am waiting until
the program is done and then will go back and review queries to look for
bottlenecks.
Thanks to all of you!!
Madi
---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings
