Re: Improving speed of query

Hi,

You could run 2 queries separatly and asynchrouneously
1) the limit 10
2) the count

While the limit 10 query would be showned instanteneously, the web table
would way for the count to build the pagination

Le lun. 26 sept. 2016 à 20:59, Leonardo M. Ramé <l(dot)rame(at)griensu(dot)com> a
écrit :

> Hi, I'm using a query to fill a paginated table. The task involves
> filtering, sorting, limit, offset and count of all rows (to determine
> the number of pages).
>
> My current query is this:
>
> select count(*) over() as totalrows,
> case when (d.filepath is not null) then '1' else '0' end as HasDocument,
> e.idtask, e.site, e.pacs, e.studyuid, e.accessionnumber,
> e.patientemail, e.refphysicianemail, e.sent, e.password, e.created,
> e.institutionname, e.patientname, e.studydate, e.studytime,
> e.proceduredescription, e.performingphysician, e.referringphysician,
> e.informantphysician, e.forcesend, e.sentdate, e.md5identifier,
> e.read, e.patientid
> from emailtasks e
> join sites s on s.identifier = e.site
> left join documents_current d on d.idtask=e.idtask
> where s.idsite = 34
> order by e.idtask desc
> limit 10 offset 0;
>
> I've made several indexes, and they really fast. The problem here is the
> window function count(*) to get the total number of rows.
>
> Here's the explain analyze result:
>
> Limit (cost=0.84..57.98 rows=10 width=310) (actual
> time=36075.589..36079.371 rows=10 loops=1)
> -> WindowAgg (cost=0.84..84302.61 rows=14754 width=310) (actual
> time=36075.581..36079.356 rows=10 loops=1)
> -> Nested Loop Left Join (cost=0.84..84118.19 rows=14754
> width=310) (actual time=0.085..30639.311 rows=258839 loops=1)
> -> Nested Loop (cost=0.42..39977.25 rows=10170
> width=260) (actual time=0.071..10308.789 rows=146782 loops=1)
> Join Filter: (e.site = s.identifier)
> Rows Removed by Join Filter: 66794
> -> Index Scan using idx_emailtasks_idtask on
> emailtasks e (cost=0.42..36772.35 rows=213576 width=260) (actual
> time=0.013..9929.527 rows=213576 loops=1)
> -> Materialize (cost=0.00..1.27 rows=1 width=16)
> (actual time=0.000..0.001 rows=1 loops=213576)
> -> Seq Scan on sites s (cost=0.00..1.26
> rows=1 width=16) (actual time=0.024..0.026 rows=1 loops=1)
> Filter: (idsite = 34)
> Rows Removed by Filter: 20
> -> Index Scan using idx_documents_current_idtask on
> documents_current d (cost=0.42..4.32 rows=2 width=54) (actual
> time=0.092..0.136 rows=2 loops=146782)
> Index Cond: (idtask = e.idtask)
> Total runtime: 36106.813 ms
> (14 rows)
>
> What strategy do you recommend for speeding up this query?.
>
> Regards,
> --
> Leonardo M. Ramé
> Medical IT - Griensu S.A.
> Av. Colón 636 - Piso 8 Of. A
> X5000EPT -- Córdoba
> Tel.: +54(351)4246924 +54(351)4247788 +54(351)4247979 int. 19
> Cel.: +54 9 (011) 40871877
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>