[ckan-dev] Preview from datastore, count(*) takes very long time on large resources.

David Raznick david.raznick at okfn.org
Wed Dec 11 13:46:07 UTC 2013


On 11 December 2013 11:48, Tom Kralidis <tomkralidis at gmail.com> wrote:
> 1./ apparently this gets better in PostgreSQL 9.2+?
> https://wiki.postgresql.org/wiki/Index-only_scans

Not always for count(*) over the whole table, but better for count(*)
on a subset.
>
> 2./ how would using the statistics table to generate an estimated count look?
>

http://wiki.postgresql.org/wiki/Count_estimate

so basically

SELECT reltuples FROM pg_class WHERE relname = 'yourtable'


>
> On Wed, Dec 11, 2013 at 6:01 AM, David Raznick <david.raznick at okfn.org> wrote:
>> Hello
>>
>> My preference would be to use the statistics (which is fast) and if
>> the stats say its over a certain threshold say 100,000 rows then use
>> that number as an estimate, otherwise query for the actual count.
>> Adding a table with counts has performance costs when writing and its
>> hard to do correctly on concurrent changes as you need locking.
>>
>> David
>>
>> On 11 December 2013 10:09, Henrik Aagaard Sørensen <BU1G at tmf.kk.dk> wrote:
>>> That is true.
>>>
>>> I don't know if it is a common issue. If so, perhaps it could be an idea to create a table which stored count's on other tables.
>>> And creating a general trigger, which could update the table-counts on insert and delete?
>>> _______________________________________________
>>> ckan-dev mailing list
>>> ckan-dev at lists.okfn.org
>>> http://lists.okfn.org/mailman/listinfo/ckan-dev
>>> Unsubscribe: http://lists.okfn.org/mailman/options/ckan-dev
>> _______________________________________________
>> ckan-dev mailing list
>> ckan-dev at lists.okfn.org
>> http://lists.okfn.org/mailman/listinfo/ckan-dev
>> Unsubscribe: http://lists.okfn.org/mailman/options/ckan-dev
> _______________________________________________
> ckan-dev mailing list
> ckan-dev at lists.okfn.org
> http://lists.okfn.org/mailman/listinfo/ckan-dev
> Unsubscribe: http://lists.okfn.org/mailman/options/ckan-dev



More information about the ckan-dev mailing list