On Tue, Oct 19, 2010 at 7:56 PM, Merlin Moncure <mmoncure@xxxxxxxxx> wrote: > On Mon, Oct 18, 2010 at 1:16 AM, AI Rumman <rummandba@xxxxxxxxx> wrote: >> At present for reporting I use following types of query: >> select crm.*, crm_cnt.cnt >> from crm, >> (select count(*) as cnt from crm) crm_cnt; >> Here count query is used to find the total number of records. >> Same FROM clause is copied in both the part of the query. >> Is there any other good alternative way to get this similar value? > > Probably the best way to do this type of thing is handle it on the > client. However, if you want to do it this way and your from clause > is more complex than 'from table', you can possibly improve on this > with a CTE: > > with q as (select * from <something expensive>) > select q.* q_cnt.cnt from q, (select count(*) as cnt from q) q_cnt; > > The advantage here is that the CTE is materialized without having to > do the whole query again. This can be win or loss depending on the > query. What about select crm.*, sum(1) over () as crm_count from crm limit 10; -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
