Query performance on table with indices

General
1

Hello everyone,

I am running Yugabyte 2.12.1.0 on single node, and I am in the process of porting our database from PostgreSQL to Yugabyte. In doing so, I have noticed that certain queries are taking much more time to complete in Yugabyte than in PostgreSQL for the exact same data set. Here is an example of a table I am querying:

CREATE TABLE IF NOT EXISTS demo (name VARCHAR, ts TIMESTAMP(3) WITH TIME ZONE NOT NULL);
CREATE INDEX IF NOT EXISTS demo_name_idx ON demo (name);
CREATE INDEX IF NOT EXISTS demo_like_name_idx ON demo (name varchar_pattern_ops);
CREATE INDEX IF NOT EXISTS demo_ts_idx ON demo (ts DESC);

Then I insert rows in this table with the following statements from within ysqlsh:
DO $$
DECLARE
i INTEGER;
j INTEGER;
baseTime TIMESTAMP(3) WITH TIME ZONE;
BEGIN
baseTime := ‘2022-03-10 11:00:01.000+02’;
FOR i IN 1…1000
LOOP
FOR j IN 1…1000
LOOP
INSERT INTO demo (name, ts) SELECT ‘object_’ || i, baseTime - CAST(j || ‘milliseconds’ AS interval);
END LOOP;
END LOOP;
END $$;

The following are examples of queries that take significantly longer to complete on YugabyteDB than in PostgreSQL:

  1. SELECT name, ts FROM demo WHERE ts BETWEEN ‘2022-03-10 11:00:00.200+02’ AND ‘2022-03-10 11:00:00.500+02’ ORDER BY ts DESC; – 10.3 sec on YB vs. 235 ms on PG
  2. SELECT name, ts FROM demo WHERE ts BETWEEN ‘2022-03-10 11:00:00.200+02’ AND ‘2022-03-10 11:00:00.500+02’ AND name LIKE ‘object_62%’ ORDER BY ts desc; – 10.3 sec on YB vs. 49 ms on PG
  3. SELECT name, ts FROM demo WHERE ts >= ‘2022-03-10 11:00:00.200+02’ ORDER BY ts DESC; – 27.8 sec on YB vs. 578 ms on PG
  4. SELECT name, ts FROM demo WHERE name LIKE ‘object_62%’ ORDER BY ts DESC; – 34.2 sec on YB vs. 58 ms on PG

I ran “EXPLAIN ANALYZE” on the above queries, and for the queries (1) and (3) the query plan is the same for both YugabyteDB and PostgreSQL (index scan on demo_ts_idx index). For queries (2) and (4), in YugabyteDB index scan is performed using demo_ts_idx index and then the filter ((name)::text ~~ ‘object_62%’::text) is applied. No sequential scan was found in any of the query execution plans.

I tried substituting VARCHAR with TEXT in my table definition (and using text_pattern_ops in the relevant index), but the result was the same. I also tried bringing up tserver with flag --read_buffer_memory_limit=0 (unlimited), but there was still no improvement. Neither did anything change when I attempted to increase the shared buffers on the postgres server that YugabyteDB starts up (using “–ysql_pg_conf=shared_buffers=2GB” flag) .

I would greatly appreciate any guidance and advice with respect to what we need to specify and/or change (either in our DB schema, or in the configuration of the YugabyteDB servers) to achieve performance improvement on the above queries in YugabyteDB.

Thank you

2

@vlst

Why no primary key on the table? Is name not unique?

3

This example mimics how our actual table was defined (i.e., without primary key). If we were to define a primary key, I would need to add a 3rd column type_id of type NUMERIC to my example (which would mimic another column we have in our actual table), and then the primary key would consist of the combination of the 3 columns name, ts, and type_id.

4

Hi @vlst, even in single node, YugabyteDB uses the distributed architecture and some operations are slower than in PostgreSQL. For example, we do not use the shared buffers because it cannot scale out.
But there are ways to optimize for this

  • the index on “name” must be a range to allow queries on a prefix. This is done by declaring ASC or DESC as you did for “ts”
  • when reading lot of rows by index, we should put all columns into the index to do an Index Only Scan (long details in this blog post)

I think you will get better performance by defining the indexes as:

CREATE INDEX IF NOT EXISTS demo_name_idx ON demo (name ASC);
CREATE INDEX IF NOT EXISTS demo_ts_idx ON demo (ts DESC, NAME ASC);
5

Will you use the type_id in queries? How many unique type_id values do you have?

6

Thank you for your replies. For now, I am concentrating on the simpler case where the demo table does not have the type_id column.
I used the suggested index definitions, and indeed there was improvement, but not in all of the 4 queries I am testing. Then, after having read the post about Index Only Scan, I further revised the index definitions (except for the demo_ts_idx), so now the indices on the demo table are altogether the following:
CREATE INDEX IF NOT EXISTS demo_name_idx ON demo (name ASC, ts DESC);
CREATE INDEX IF NOT EXISTS demo_like_name_idx ON demo (name text_pattern_ops ASC, ts DESC);
CREATE INDEX IF NOT EXISTS demo_ts_idx ON demo (ts DESC, name ASC);

With these indices, the queries (1) and (2) complete in approximately 3 sec, the query (3) takes about 8.5 sec, and the query (4) takes around 11 sec. While this is definite improvement compared to what I had originally, I would like to know if it is possible to further optimize execution of the queries (3) and (4) especially. Note that EXPLAIN ANALYZE on these queries showed “Index Only Scan”.

7

I’ve taken (1) as an example. Yes 3 seconds. The plan with Index Only Scan is optimal but that’s still 300000 rows to fetch. It is slower than PostgreSQL which reads that by blocks from local disks. YugabyteDB is optimized for OLTP. What is your use case reading 300000 rows?

One thing, with range sharding, there is currenly only one shard. I don’t think it is parallelized, yet, but it can make sens to split in multiple tables, like:

drop index demo_name_idx;
CREATE INDEX IF NOT EXISTS demo_name_idx ON demo (name ASC, ts DESC) split at values
(('object_210','2022-03-10 09:00:00.999+00'),('object_323','2022-03-10 09:00:00.999+00'),('object_436','2022-03-10 09:00:00.999+00'),('object_549','2022-03-10 09:00:00.999+00'),('object_661','2022-03-10 09:00:00.999+00'),('object_774','2022-03-10 09:00:00.999+00'),('object_887','2022-03-10 09:00:00.999+00'));

For info, I used this to generate the split points:

select '('||string_agg("k",',')||')' from (
select lag("#") over(order by   name  ) "-", *    from (
select ntile(8) over(order by   name  ) "#", name, '('''||  name  ::text||''','''||  ts  ::text||''')'
--           ^                  ^
--           splits             split column ^              ^--------------------------------^
"k" from demo) with_ntile ) with_lag where "#">"-" ;

But the most important is to understand the purpose of such query to see if it can be done differently.

8

Can we get the whole table, what you think you’ll actually need in production? And can you explain a little what data the table stores?

9

The real table is used for logging various events that occur within our application domain. Each entry in the table is for a particular object, timestamp, and event type. There are many other fields in this table, for example the message of the event (if applicable), the severity of the event, etc. The application user can run reports on these events, such as retrieving all the events for an object that have occurred since a certain point in time. For running such interactive reports, we do not actually retrieve all the rows, but use LIMIT and OFFSET in the query. However, the user may also choose to export the report to a *.csv file, in which case all the applicable rows are retrieved.

The actual queries that are performed in our application perform a join between 8 tables (one of which is actually a view with only the aforementioned table in the FROM clause). I had started out with these queries where I had seen very long query duration times in YugabyteDB, even when these queries were using LIMIT 50. So, in an effort to understand the cause of this behavior, I skipped the view and the joins from the other tables, and kept only a stripped down version of the base table (i.e, “demo”) with only two columns.

I am not quite sure I have understood the suggestion of creating a split index. Would we need to periodically drop the index and re-create it (since the values of the ts column used for the split points would change as time goes by)?

Also, you mention that with range sharding, there is currently only 1 shard. So, if an index column is defined as ASC or DESC, there is no longer horizontal scaling?

Finally, I would appreciate if you could explain what you mean by “parallelized”: when does it apply, and how does this affects performance.

Thank you

10

I’m answering quickly a few points, will re-read the rest later, thanks for the details

you mention that with range sharding, there is currently only 1 shard

Yes, with range sharding we currently have only one split when creating the data. But to scale horizontally there are manual and automatic ways to split them later:

But for benchmarking, better to split it manually to have something reproducible.

what you mean by “parallelized”

As we don’t have parallel query (yet), once it is split, you can query different ranges in parallel for the case where all rows are retrieved. This requires application code define the ranges and start threads.

very long query duration times in YugabyteDB, even when these queries were using LIMIT 50

Should have a look at the execution plan. 50 rows by Index Only Scan should be fast

11

Now that I see it… OFFSET is always a bad idea. Bad in monolithic databases, worse in distributed ones. I had a blog post in draft about the correct way to do pagination so I just publish it - it should show the right index usage for this: Efficient pagination in YugabyteDB & PostgreSQL 📃🐘🚀 - DEV Community