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

Wed Dec 11 11:48:54 UTC 2013

1./ apparently this gets better in PostgreSQL 9.2+?
https://wiki.postgresql.org/wiki/Index-only_scans

2./ how would using the statistics table to generate an estimated count look?

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