Search Postgresql Archives

Re: Slow COUNT

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

 



Poul,

2 things....first, why do you think it will have an impact on inserts?

And the second one....use InnoDb tables in MySQL, and you will have the same than with PostgreSQL, it's because of MVCC

Best regards

Rodrigo

Poul Møller Hansen wrote:

I can see it has been discussed before, why COUNT(*) tends to be slow on PostgreSQL compared with ex. MySQL. As far as I understood it has something to do with missing numbering on the rows in the indexes and that there should be plenty of reasons not to implement that in PostgreSQL, not that I found an explanation.
However I can imagine it will have an impact on inserts.

My questions is, which statements can use to count the rows faster ?
32 secs compared to 10 ms !


Thanks,
 Poul


db=# explain analyze select count(*) from my.table;
                                                         QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------- Aggregate (cost=60008.28..60008.28 rows=1 width=0) (actual time=32028.469..32028.474 rows=1 loops=1) -> Seq Scan on table (cost=0.00..54962.62 rows=2018262 width=0) (actual time=14.492..19592.014 rows=2018252 loops=1)
 Total runtime: 32028.750 ms
(3 rows)

db=# explain analyze select count(*) from my.table where node = '1234567890';

QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------- Aggregate (cost=50023.14..50023.14 rows=1 width=0) (actual time=1790.967..1790.971 rows=1 loops=1) -> Index Scan using idx_node_date_id on table (cost=0.00..49968.76 rows=21753 width=0) (actual time=80.218..1570.747 rows=34648 loops=1)
         Index Cond: ((node)::text = '1234567890'::text)
 Total runtime: 1792.084 ms
(4 rows)

mysql>select count(*) from table;
+----------+
| count(*) |
+----------+
|  2018160 |
+----------+
1 row in set (0.01 sec)

mysql>select count(*) from table where node = '1234567890';
+----------+
| count(*) |
+----------+
|    34648 |
+----------+
1 row in set (0.23 sec)



---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend



[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