Solved! Was: (subquery/alias question) - Mailing list pgsql-general
From | Madison Kelly |
---|---|
Subject | Solved! Was: (subquery/alias question) |
Date | |
Msg-id | 46FA5A1B.5000009@alteeve.com Whole thread Raw |
In response to | Re: subquery/alias question (Alvaro Herrera <alvherre@commandprompt.com>) |
List | pgsql-general |
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
pgsql-general by date: