Re: Slow COUNT - Mailing list pgsql-general

And InnoDB is actually quite a bit worse than PostgreSQL (ignoring the
slight difference in row numbers)

InnoDB:

olp_live> select count(*) from team_players;
+----------+
| count(*) |
+----------+
|   465004 |
+----------+
1 row in set (1.54 sec)


PostgreSQL:

olp_live=# select count(*) from team_players;
+--------+
| count  |
+--------+
| 464747 |
+--------+
(1 row)

Time: 934.935 ms


Rodrigo Gonzalez wrote:

> 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
>>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings
>
> .
>

--
_____________________________________________
G l o b a l   D i a g n o s t i c s    I n c.
Andrew Schmidt            t.416-304-0049 x206
aschmidt@lifescale.com         f.866-697-8726
_____________________________________________