[Postgres-xl-bugs] Slow OLAP Query over PostgresXL 9.5 R1.1 Stable

Koichi Suzuki koichi.dbms at gmail.com
Mon Aug 1 00:18:12 PDT 2016


In fact, the coordinator does not send plans to datanodes.   It simply
transfers query statement and each datanode builds its own plan based on
the local statistics.

Could you try to get a plan at datanode?   You can do this using
"auto_explain".

Thank you;
---
Koichi Suzuki

---
Koichi Suzuki
https://www.linkedin.com/in/koichidbms


2016-08-01 16:12 GMT+09:00 Sergio Hernández Martínez <serhermar at hotmail.com>
:

> Hi for Everybody,
>
> After install last version XL 9.5 R1.2 and reload 2TB data, i have the
> same problem with SELECT COUNT(0) FROM TABLE_NOT_PARTITIONED and SELECT
> COUNT(0) FROM TABLE_PARTITIONED, in coordinator the query take 40 minutes
> and in all datanode the query take 33 seconds.
>
> The plain of queries are:
>
> COORDINATOR:
>
> 'Aggregate  (cost=212500002.00..212500002.01 rows=1 width=0)'
> '  Output: count((count(0)))'
> '  ->  Remote Subquery Scan on all
> (datanode_node01,datanode_node02,datanode_node03,datanode_node04)
>  (cost=0.00..210000002.00 rows=1000000000 width=0)'
> '        Output: count(0)'
> '        ->  Aggregate  (cost=0.00..210000002.00 rows=1 width=0)'
> '              Output: count(0)'
> '              ->  Seq Scan on
> public.TABLE_NOT_PARTITIONED  (cost=0.00..210000002.00 rows=1000000000
> width=0)'
> '                    Output: columns.....'
>
>
> DATANODE:
>
> 'Aggregate  (cost=7116543.17..7116543.18 rows=1 width=0)'
> '  Output: count(0)'
> '  ->  Index Only Scan using idx_date_fraud on
> public.TABLE_NOT_PARTITIONED  (cost=0.57..6491575.37 rows=249987120
> width=0)'
> '        Output: date_fraud'
>
> After copy all data i launch vacuumdb command:
>
> vacuumdb -p 5432 -a
> vacuumdb -p 5432 -z testing_database
>
> I think the coordinator don't have the table's statistics updated and the
> planner not know to do and launch full scan plan in Datanodes.
>
> It can be a forgotten some settings? or is a bug?
>
> Thank you for everybody
>
> ------------------------------
> From: serhermar at hotmail.com
> To: pavan.deolasee at gmail.com
> CC: postgres-xl-bugs at lists.sourceforge.net
> Subject: RE: [Postgres-xl-bugs] Slow OLAP Query over PostgresXL 9.5 R1.1
> Stable
> Date: Wed, 27 Jul 2016 13:34:44 +0200
>
>
> Hi Pavan,
>
> Thank you very much for the rapid response,
>
> when it was made public the next minor release?
>
> Congratulations for the great work with Postgres XL 9.5.
>
> Thank you very much.
>
> ------------------------------
> From: pavan.deolasee at gmail.com
> Date: Wed, 27 Jul 2016 12:47:28 +0530
> Subject: Re: [Postgres-xl-bugs] Slow OLAP Query over PostgresXL 9.5 R1.1
> Stable
> To: serhermar at hotmail.com
> CC: postgres-xl-bugs at lists.sourceforge.net
>
>
>
> On Wed, Jul 27, 2016 at 12:41 PM, Sergio Hernández Martínez <
> serhermar at hotmail.com> wrote:
>
> Hi for everybody,
>
> I have 4 nodes with Postgres XL 9.5 R1.1, the architecture are:
>
> Node1: GTM, Coordinator, Datanode
> Node2: GTM_slave, GTM_Proxy, Coordinator, Datanode
> Node3: GTM_Proxy, Coordinator, Datanode
> Node4: GTM_Proxy, Coordinator, Datanode
>
> I have a 1TB Table and a 1TB Table with partitions and the query is
> "Select count(*) from TABLE" and "Select count(*) from
> table_with_partitions", in Postgresql.conf of Coordinators and Datanodes i
> have the property: constraint_exclusion = on
>
> The execution time of these queries in the Datanodes is 40 seconds. The
> problem is the execution time of these queries in the Coordinators is 45
> minutes.
>
> The explain plan in coordinator is:
>
> Table without partitions:
>
> Coordinators
>
> 'Aggregate  (cost=212500002.00..212500002.01 rows=1 width=0)'
> '  Output: count((count(0)))'
> '  ->  Remote Subquery Scan on all
> (datanode_node01,datanode_node02,datanode_node03,datanode_node04)
>  (cost=0.00..210000002.00 rows=1000000000 width=0)'
> '        Output: count(0)'
> '        ->  Aggregate  (cost=0.00..210000002.00 rows=1 width=0)'
> '              Output: count(0)'
> '              ->  Seq Scan on public.table  (cost=0.00..210000002.00
> rows=1000000000 width=0)'
> '                    Output: columns..........'
>
>
>
> My gut feel is that you might have been affected by a bug which is now
> committed and will be available in the next minor release.
>
> commit 8ca720f90f7c9cbd54a8ae4e9a2d982940005bdb
> Author: Pavan Deolasee <pavan.deolasee at gmail.com>
> Date:   Wed May 25 19:06:51 2016 +0530
>
>     Do not reset stats on the coordinator upon create of a new index or
> cluster.
>
> To verify, run an ANALYZE on the coordinator for the involved tables to
> rebuild planner stats and see if that helps.
>
> Thanks,
> Pavan
>
>
> --
>  Pavan Deolasee                   http://www.2ndQuadrant.com/
>  PostgreSQL Development, 24x7 Support, Training & Services
>
>
> ------------------------------------------------------------------------------
>
> _______________________________________________
> Postgres-xl-bugs mailing list
> Postgres-xl-bugs at lists.sourceforge.net
> https://lists.sourceforge.net/lists/listinfo/postgres-xl-bugs
>
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.postgres-xl.org/private.cgi/postgres-xl-bugs-postgres-xl.org/attachments/20160801/cd34b273/attachment.htm>


More information about the postgres-xl-bugs mailing list