Search Postgresql Archives

Solved! Was: (subquery/alias question)

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux