REVIEW: pg_stat_statements with query tree based normalization - Mailing list pgsql-hackers

This is a short review of pg_stat_statements based on quick testing of the feature.

1. Installation: after managing to actually build PostgreSQL and contrib modules + changing
shared_preload_libraries to include pg_stat_statements I got this error:
FATAL:  could not create shared memory segment: Invalid argument
DETAIL:  Failed system call was shmget(key=5431001, size=34627584, 03600)

So, I needed to rise my SMH limits. I guess there isn't anything surprising or erroneous about
this but figured this is worth mentioning.

2. Usability: - If you have two similarly named tables in different schemas, for example public.tbl and
some_schema.tbl these tables will get different entries in pg_stat_statements. However, the
table names are not schema-qualified, so it is impossible to see which table is which.

# select query, calls from pg_stat_statements where query like 'select%test%';       query        | calls
---------------------+-------select * from test; |     4select * from test; |     2

# select * from tmp.test;
# select query, calls from pg_stat_statements where query like 'select%test%';       query        | calls
---------------------+-------select * from test; |     5select * from test; |     2

# select * from test;
# select query, calls from pg_stat_statements where query like 'select%test%';       query        | calls
---------------------+-------select * from test; |     5select * from test; |     3
   - It would be nice from user perspective to transform "where id in (list of values)" to
"where id in(?)" always, regardless of the length of the list. Now "where id in (1, 2)" is
grouped to different pool than "where id in (1, 2, 3)".

3. I tried to run Django's test suite a few times and see if there would be any unexpected
behavior. Some results (note that I haven't tried to reproduce this error on master
without the patch):

test_django_testdb_default=# SELECT "aggregation_publisher"."id", "aggregation_publisher"."name",
"aggregation_publisher"."num_awards",MIN("aggregation_book"."pubdate") AS "earliest_book" FROM "aggregation_publisher"
LEFTOUTER JOIN "aggregation_book" ON ("aggregation_publisher"."id" = "aggregation_book"."publisher_id") GROUP BY
"aggregation_publisher"."id","aggregation_publisher"."name", "aggregation_publisher"."num_awards" HAVING NOT
(MIN("aggregation_book"."pubdate")IS NULL) ORDER BY "earliest_book" ASC; 

ERROR:  unrecognized node type for havingclause node: 315
test_django_testdb_default=# \d aggregation_publisher                                  Table
"public.aggregation_publisher" Column   |          Type          |                             Modifiers
              
------------+------------------------+--------------------------------------------------------------------id         |
integer               | not null default nextval('aggregation_publisher_id_seq'::regclass)name       | character
varying(255)| not nullnum_awards | integer                | not null 
Indexes:   "aggregation_publisher_pkey" PRIMARY KEY, btree (id)
Referenced by:   TABLE "aggregation_book" CONSTRAINT "aggregation_book_publisher_id_fkey" FOREIGN KEY (publisher_id)
REFERENCESaggregation_publisher(id) DEFERRABLE INITIALLY DEFERRED 


The time used for insert statements seems suspiciously low. Maybe PostgreSQL is just faster than I thought :)

query      | INSERT INTO "django_content_type" ("id", "name", "app_label", "model") VALUES (?, ?, ?, ?)
calls      | 5490
total_time | 0.823119000000003

Multi-values inserts do not seem to be normalized:
query      | INSERT INTO "custom_pk_business_employees" ("business_id", "employee_id") VALUES ('Sears', 456), ('Sears',
123)
calls      | 1256
total_time | 0.619693

I did not see any noticeable difference in runtimes with pg_stat_statements installed or uninstalled (as extension).
Not tested on master without the patch at all.

Overall the feature seems to be really useful.
- Anssi