[YSQL] I meet very pool poformance comparing with single Postgres. If there were some addtional param I've miss?

[IIvyPy]

Jira Link: DB-3924

Description

when I exec an select-SQL, It seems the YbDB is always slower than Postgres(single). here is YbDB:

where here is Postgres:

here is my DDL:

CREATE TABLE author_dns.dns_ip_location (
    id bigserial NOT NULL,
    ip_mask inet NOT NULL,
    view_level7 varchar(32) NOT NULL,
    "type" int2 NOT NULL,
    is_deleted bool NOT NULL DEFAULT false,
    remark varchar(1024) NULL,
    CONSTRAINT dns_ip_location_pkey PRIMARY KEY (id)
);
CREATE INDEX dns_ip_location_view_level7_idx ON author_dns.dns_ip_location USING lsm (view_level7 HASH);

-- author_dns.dns_ip_location foreign keys
ALTER TABLE author_dns.dns_ip_location ADD CONSTRAINT fk_dns_ip_geo_dns_view_1 FOREIGN KEY (view_level7) REFERENCES author_dns.dns_view_mapping(view_level7);

[IIvyPy]

Please Help me, thank you.

[IIvyPy]

I create ybDB using kubenetes. like this: kubectl create namespace yb-demo helm install yb-demo yugabytedb/yugabyte \ --version 2.15.2 \ --set resource.master.requests.cpu=0.5,resource.master.requests.memory=0.5Gi,\ resource.tserver.requests.cpu=0.5,resource.tserver.requests.memory=0.5Gi,\ replicas.master=1,replicas.tserver=1 --namespace yb-demo

[ddorian]

@IIvyPy what's your query? And how many rows in the table?

[IIvyPy]

@IIvyPy what's your query? And how many rows in the table?

query is just: select count(*) from author_dns.dns_ip_location; the rows is: 2712168

[ddorian]

@IIvyPy the database is distributed by default and is not optimized as an OLAP but mostly for OLTP workloads: https://docs.yugabyte.com/preview/faq/general/#when-is-yugabytedb-not-a-good-fit. It will be slower than a single PostgreSQL instance in this scenario.

[ddorian]

kubectl create namespace yb-demo helm install yb-demo yugabytedb/yugabyte \ --version 2.15.2 \ --set resource.master.requests.cpu=0.5,resource.master.requests.memory=0.5Gi,\ resource.tserver.requests.cpu=0.5,resource.tserver.requests.memory=0.5Gi,\ replicas.master=1,replicas.tserver=1 --namespace yb-demo

Please read the hardware requirements: https://docs.yugabyte.com/preview/deploy/checklist/#hardware-requirements.

You are using the database in the worst way possible, OLAP + extremely low hardware + comparing against a non-distributed PostgreSQL.