WendelLana commented on issue #1004:
URL: https://github.com/apache/age/issues/1004#issuecomment-1699864880
## Benchmarking of label IDs in an array with identical data
In this benchmarking, I conducted tests on three tables, each containing
identical data, with the only difference being the data type, changed from
`int4` (integer) to `int4[]` (integer array).
### Setup
**Creation of tables**
```sql
CREATE TABLE labels_test1.ag_label_vertex
(
id bigint NOT NULL,
label_id INT4,
PRIMARY KEY (id)
);
CREATE TABLE labels_test1.ag_label_vertex_array
(
id bigint NOT NULL,
label_id INT4[],
PRIMARY KEY (id)
);
```
**Populating the tables**
- The label_id array only contains 1 integer.
- Created 1000000 (one million) vertices (rows) for each table, same data in
both tables.
- Label IDs range from 1 to 10.
**Table 1 sample - ag_label_vertex**
```
SELECT * FROM labels_test1.ag_label_vertex LIMIT 10;
id | label_id
----+----------
1 | 9
2 | 5
3 | 10
4 | 2
5 | 4
6 | 5
7 | 3
8 | 4
9 | 4
10 | 5
(10 rows)
```
**Table 2 sample - ag_label_vertex_array**
```
SELECT * FROM labels_test1.ag_label_vertex_array LIMIT 10;
id | label_id
----+----------
1 | {9}
2 | {5}
3 | {10}
4 | {2}
5 | {4}
6 | {5}
7 | {3}
8 | {4}
9 | {4}
10 | {5}
(10 rows)
```
### Queries that are tested
**Query 1 - MATCH (:1)**
```sql
EXPLAIN (ANALYZE, VERBOSE, BUFFERS)
SELECT * FROM labels_test1.ag_label_vertex
WHERE label_id = 1;
EXPLAIN (ANALYZE, VERBOSE, BUFFERS)
SELECT * FROM labels_test1.ag_label_vertex_array
WHERE '{1}'::int4[] <@ label_id;
```
**Query 2 - MATCH (:1|2)**
```sql
EXPLAIN (ANALYZE, VERBOSE, BUFFERS)
SELECT * FROM labels_test1.ag_label_vertex
WHERE label_id = 1 OR label_id = 2;
EXPLAIN (ANALYZE, VERBOSE, BUFFERS)
SELECT * FROM labels_test1.ag_label_vertex_array
WHERE '{1}'::int4[] <@ label_id OR '{2}'::int4[] <@ label_id;
```
### Indices
I've created a table to test the queries with GIN index.
```sql
CREATE INDEX idx_label_array ON labels_test1.ag_label_vertex_array_gin USING
gin(label_id);
```
### Performance tests
Execution time of each query for the table ag_label_vertex (Integer Column)
and ag_label_vertex_array (Array Column), also a test with GIN index.
| | Integer Column | Array Column | Array with Index |
|---|---|---|---|
| Query 1 | 180.381 ms | 305.785 ms | 245.435 ms |
| Query 2 | 189.958 ms | 312.840 ms | 230.046 ms |
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: dev-unsubscr...@age.apache.org
For queries about this service, please contact Infrastructure at:
us...@infra.apache.org
