Issue: Slow performance of execute_sql()
Author: ssrebelious
State: closed
Created: 2018-09-11 14:23:43

I have a table `Element` in PostgreSQL database that has over 60 000 records in it and I need to perform a full-text-search for the column `name`.

```sql
CREATE TABLE public."element" (
	id serial NOT NULL,
	"name" varchar(400) NOT NULL,
	CONSTRAINT element_pkey PRIMARY KEY (id)
);
CREATE INDEX element_name_tsvector_idx ON public.element 
USING gin (to_tsvector('russian'::regconfig, (name)::text)); -- this index speeds up full-text-search from 1.5 s to 2 ms 
```
Following query in database console is executed in 2 ms when it uses the index above and in 1.5 s when there is no such index.

```sql
SELECT id, name, ts_rank(vector, query) AS rank
FROM element, to_tsvector('russian', name) vector, 
        to_tsquery('russian', 'транспортировка:* & эмульсия:* & 1') query
WHERE to_tsvector('russian', name) @@ query
ORDER BY rank desc
LIMIT 20
```
I want to port this query to peewee-enabled app using straight-forward approach like that.
```python
from datetime import datetime

from peewee import PostgresqlDatabase, Model

pg_db = PostgresqlDatabase(**connection_kwargs)


FULL_TEXT_SEARCH = '''
    SELECT id, {search_field}, ts_rank(vector, query) AS rank
    FROM {table_name}, to_tsvector('russian', {search_field}) vector, 
        to_tsquery('russian', %s) query
    WHERE to_tsvector('russian', {search_field}) @@ query
    ORDER BY rank DESC
    LIMIT 20
'''

class Element(Model):
    
    name = CharField()
    
    class Meta:
        database = pg_db
        
    @classmethod
    def full_text_search(cls, column_name: str, query_string: str):
        kwargs = dict(
            table_name=cls._meta.db_table,
            search_field=column_name
        )
        query = FULL_TEXT_SEARCH.format(**kwargs)
        cursor = cls._meta.database.execute_sql(
            query,
            (query_string,)
        )
        result = cursor.fetchall()
        cursor.close()
        return result
```
Unfortunately, `full_text_search()` method takes abysmal 1.7 seconds to execute (without the index it takes 3.5 seconds), of which only 2 ms is spent on Postgres side. It is obvious and a profiler says that the most of the time is used by `execute_sql`. Is there something I can do to speed up the process? I'm using peewee 2.10.2, but 3.7 seems to have the same execution time. 

Since you're literally just using `execute_sql()` and not using any of the query-builder APIs, you should note that Peewee is just using `psycopg2` the Python driver.

I'd suggest that you might raise this issue over on `psycopg2` since I don't think this reflects an actual Peewee performance issue.

Your logic is legit, but it seems that there is no performance issue on the `psycopg2` side.
```python
start_time = datetime.now()
conn = psycopg2.connect(**kwargs)
with conn.cursor() as cursor:
    cursor.execute(
        ''' SELECT id, name, ts_rank(vector, query) AS rank 
            FROM element, to_tsvector('russian', name) vector, 
                 to_tsquery('russian', %s) query 
            WHERE to_tsvector('russian',  name) @@ query    
            ORDER BY rank DESC    
            LIMIT 20''', 
        ('транспортировка:* & эмульсия:* & 1',)
    )
    result = cursor.fetchall()
conn.close()
finish_time = datetime.now() - start_time
print(finish_time)
```
gives me `0:00:00.01233`

But the code is literally the same...

```python
    def execute_sql(self, sql, params=None, commit=SENTINEL):
        logger.debug((sql, params))
        if commit is SENTINEL:
            if self.in_transaction():
                commit = False
            elif self.commit_select:
                commit = True
            else:
                commit = not sql[:6].lower().startswith('select')

        with __exception_wrapper__:
            cursor = self.cursor(commit)
            try:
                cursor.execute(sql, params or ())
            except Exception:
                if self.autorollback and not self.in_transaction():
                    self.rollback()
                raise
            else:
                if commit and not self.in_transaction():
                    self.commit()
        return cursor
```

You could try `.execute_sql(..., commit=False)` -- and this is Peewee 3.x, but you should be able to figure out the equivalent in 2.10.

Thanks! That dropped timings to 0.1 s, but it is still 10 times slower than pure `psycopg2` request. 

*shrug*? May you can profile it and paste some output? I don't see anything obvious, though.

>  it is still 10 times slower than pure `psycopg2` request.

I had an issue in my testing setup so `peewee` and `psycopg2` were connected to the different databases, that was the reason for the time difference mentioned in my last post. Sorry for bothering. And thanks for the cool ORM :)


Shit I didn't even think to ask. Well I'm glad peewees not 10x slower. I was slightly worried bc I didn't have a good way to explain that.