I have hit a rather odd issue with prepared queries on both pg 9.3 and 9.4 beta. I have this table (copy at http://samsaffron.com/testing.db.gz) with a very odd performance profile: When I run the following prepared query it is running significantly slower than the raw counterpart: ``` select * from topics where archetype = $1 limit 1 ``` Full test harness here: ``` #include <stdio.h> #include <stdlib.h> #include <string.h> #include <errno.h> #include <sys/time.h> #include <time.h> #include <libpq-fe.h> static void exit_nicely(PGconn *conn) { PQfinish(conn); exit(1); } static double get_wall_time(){ struct timeval time; if (gettimeofday(&time,NULL)){ // Handle error return 0; } return (double)time.tv_sec + (double)time.tv_usec * .000001; } int main(int argc, char **argv) { const char *conninfo; PGconn *conn; PGresult *res; PGresult *stmt; int i; Oid textOid = 25; char *paramValues[1]; int paramLengths[1]; paramLengths[0] = 6; paramValues[0] = "banner"; if (argc > 1) conninfo = argv[1]; else conninfo = "dbname = testing"; printf("connecting database\n"); /* Make a connection to the database */ conn = PQconnectdb(conninfo); /* Check to see that the backend connection was successfully made */ if (PQstatus(conn) != CONNECTION_OK) { fprintf(stderr, "Connection to database failed: %s", PQerrorMessage(conn)); exit_nicely(conn); } stmt = PQprepare(conn, "test", "select * from topics where archetype = $1 limit 1", 1, &textOid); printf("prepared statement\n"); double start = get_wall_time(); for(i=0; i<2000; i++){ res = PQexecPrepared(conn, "test", 1, paramValues, paramLengths, NULL, 0); if (PQresultStatus(res) != PGRES_TUPLES_OK) { fprintf(stderr, "command failed: %s", PQerrorMessage(conn)); PQclear(res); exit_nicely(conn); } PQclear(res); } double finish = get_wall_time(); fprintf(stderr, "Prepared %f \n", (finish-start)); start = get_wall_time(); for(i=0; i<2000; i++){ res = PQexec(conn, "select * from topics where archetype = 'banner' limit 1"); if (PQresultStatus(res) != PGRES_TUPLES_OK) { fprintf(stderr, "command failed: %s", PQerrorMessage(conn)); PQclear(res); exit_nicely(conn); } PQclear(res); } finish = get_wall_time(); fprintf(stderr, "raw %f \n", (finish-start)); /* close the connection to the database and cleanup */ PQfinish(conn); return 0; } ``` Results: ```text sam@ubuntu pq_play % cc -o play -I/usr/include/postgresql play.c -lpq -L/usr/include/postgresql/libpq && ./play connecting database prepared statement Prepared 9.936938 Raw 1.369071 ``` So my prepared counterpart is running at an 8th the speed of the raw. I had a nightmare of a time generating a workload that exhibits the issue via script but managed to anonymise the data which is linked above. Very strangely when I run the query in "psql" it does not exhibit the issue: ```text sam@ubuntu pq_play % psql testing psql (9.3.5) Type "help" for help. testing=# prepare test as select * from topics where archetype = $1 limit 1; PREPARE testing=# explain analyze execute test('banner'); QUERY PLAN ------------------------------------------------------------------------------------------------------------------------ Limit (cost=0.29..651.49 rows=1 width=520) (actual time=0.983..0.983 rows=0 loops=1) -> Index Scan using idx11 on topics (cost=0.29..651.49 rows=1 width=520) (actual time=0.980..0.980 rows=0 loops=1) Index Cond: ((archetype)::text = 'banner'::text) Total runtime: 1.037 ms (4 rows) testing=# explain analyze select * from topics where archetype = 'banner' limit 1; QUERY PLAN ------------------------------------------------------------------------------------------------------------------------ Limit (cost=0.29..651.49 rows=1 width=520) (actual time=0.642..0.642 rows=0 loops=1) -> Index Scan using idx11 on topics (cost=0.29..651.49 rows=1 width=520) (actual time=0.641..0.641 rows=0 loops=1) Index Cond: ((archetype)::text = 'banner'::text) Total runtime: 0.673 ms (4 rows) ``` Something about running this from libpq is causing it to scan the table as opposed to scanning the index. Any idea why is this happening? (note: Rails 4.2 is moving to a pattern of using prepared statements far more often which is why I discovered this issue) ps. also posted on our meta to keep track of it: https://meta.discourse.org/t/performance-issue-with-prepared-queries/22141 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
