CODEWITHSUNDEEP
pythondatabasepostgresqlperformancepandas
cnikolaou
cnikolaou

Reputation: 3992

Fast complex SQL queries on PostgreSQL database with Python

I have a large dataset with +50M records in a PostgreSQL database that require massive calculations, inner join.

Python is the tool of choice with Psycopg2.

Running the process with fetchmany of 20,000 records takes a couple of hours to finish.

The execution needs to take place sequentially, as in each record of the 50M needs to be fetched separately, then another query (in the below example) needs to run before a result is returned and saved in a separate table.

Indexes are properly configured on each table (5 tables in total) and the complex query (that returns a calculated value - example below) takes around 240MS to return results (when the database is not under load).

Celery is used to take care of database inserts of the calculated values in a separate table.

My question is about common strategies to reduce overall running time and produce results/calculations faster. In other words, what is an effective way to go through all the records, one by one, calculate the value of a field via a second query then save the result.

UPDATE:

There is an important piece of information that I unintentionally missed mentioning while trying to obfuscate sensitive details. Sorry for that.

The original SELECT query calculates a value aggregated from different tables as follows:

SELECT CR.gg, (AX.b + BF.f)/CR.d AS calculated_field
FROM table_one CR
  LEFT JOIN table_two AX ON EX.x = CR.x
  LEFT JOIN table_three BF ON BF.x = CR.x
WHERE CR.gg = '123'
GROUP BY CR.gg;

PS: the SQL query is written by our experienced DBA so i trust that it is optimised.

Upvotes: 1

Views: 1722

Answers (3)

cnikolaou
cnikolaou

Reputation: 3992

I took @wildplasser's advice and moved the calculation operation inside the database as a function. The result has been impressively efficient to say the least and total run time dropped to minutes/~ hour.

To recap:

  • Database records are no longer fetched in the sequence mentioned earlier
  • Calculations happen inside the database via a function PostgreSQL function

Upvotes: 0

wildplasser
wildplasser

Reputation: 44240

  • don't loop over records and call the DBMS repeatedly for every record.
  • instead, let the DBMS process large chunks (preferrably: all) of data
  • and, let it spit out all the results.

Below is a snippet of my twitter-sucker(with a rather complex ugly query)

def fetch_referred_tweets(self):
    self.curs = self.conn.cursor()
    tups = ()
    selrefd = """SELECT twx.id, twx.in_reply_to_id, twx.seq, twx.created_at
    FROM(
     SELECT tw1.id, tw1.in_reply_to_id, tw1.seq, tw1.created_at
     FROM tt_tweets tw1
     WHERE 1=1
      AND tw1.in_reply_to_id > 0
      AND tw1.is_retweet = False
      AND tw1.did_resolve = False
      AND NOT EXISTS ( SELECT * FROM tweets nx
                       WHERE nx.id = tw1.in_reply_to_id)
      AND NOT EXISTS ( SELECT * FROM tt_tweets nx
                       WHERE nx.id = tw1.in_reply_to_id)
     UNION ALL
    SELECT tw2.id, tw2.in_reply_to_id, tw2.seq, tw2.created_at
     FROM tweets tw2
     WHERE 1=1
      AND tw2.in_reply_to_id > 0
      AND tw2.is_retweet = False
      AND tw2.did_resolve = False
      AND NOT EXISTS ( SELECT * FROM tweets nx
                       WHERE nx.id = tw2.in_reply_to_id)
      AND NOT EXISTS ( SELECT * FROM tt_tweets nx
                       WHERE nx.id = tw2.in_reply_to_id)
     -- ORDER BY tw2.created_at DESC
    )twx
     LIMIT %s;"""
      # -- AND tw.created_at < now() - '15 min':: interval
      # -- AND tw.created_at >= now() - '72 hour':: interval

    count = 0
    uniqs = 0
    self.curs.execute(selrefd, (quotum_referred_tweets, ) )
    tups = self.curs.fetchmany(quotum_referred_tweets)
    for tup in tups:
      if tup == None: break
      print ('%d -->> %d [seq=%d] datum=%s' % tup)
      self.resolve_list.append(tup[0] ) # this tweet
      if tup[1] not in self.refetch_tweets:
          self.refetch_tweets[ tup[1] ] = [ tup[0]] # referred tweet
          uniqs += 1

      count += 1

    self.curs.close()

Note: your query makes no sense:

  • you only select fields from the ertable
  • so, the two LEFT JOINed tables could be omitted
  • if ex and ef do contain multiple matching rows, the resultset could be larger than just all the rows selected from er, resulting in duplicateder records
  • there is a GROUP BY present, but no aggregates are in the select list
select er.gg, er.z, er.y
from table_one er
where er.gg = '123'
-- or:
where er.gg >= '123'
  and er.gg <= '456'
ORDER BY er.gg, er.z, er.y -- Or: some other ordering
    ;

Upvotes: 3

rachid
rachid

Reputation: 2486

since you are doing a join in your query, the logical thing to do is to work around it, meaning create what's known as a summary table, this summary table -residing on the database- will hold the final joined dataset, so in your python code you will just fetch/select data from it.

another way is to use materialized view link

Upvotes: 1

Related Questions