I have a table that has roughly 750,000 records. The table has 7 columns one of which is a text field where we have large text, ranging from a few hundred chars to a few KB of data. A simple select * is taking roughly 9 minutes to fetch all data.

I am new to PostgreSQL and I am not sure if this is what I should expect from it in terms of performance. If I remove the text field in which I store large data from my select query the time drops to roughly 4 minutes which again is quite high.

The Explain Analyze shows the following:

Select all columns:

Seq Scan on myTable (cost=0.00..37193.62 rows=749962 width=332)

Select all columns except the large text field:

Seq Scan on myTable (cost=0.00..37193.62 rows=749962 width=69)

I am not really sure what to conclude from this, any help in improving the performance is appreciated.

  • 1
    Please tag your Postgres version. – McNets Nov 9 '18 at 11:31
  • How do you select your data? psql? pgAdmin? Are you sure it's the selecting of the data and not the rendering of the results? Is this reading from memory or from disk? How fast/slow is the IO? – Tom V Nov 9 '18 at 12:53
  • @McNets, Its PostgreSQL 10.4, I was not able to tag it so just putting it here – Arif Eqbal Nov 9 '18 at 13:01
  • @TomV, pgAdmin I will update the timings from psql soon, however, if I remove a field with "text" datatype the difference I see is huge, I think this is going to reflect in both. Also, I have the DB installed on a Win10 machine so it ought to be reading from disk I guess. IO might be a bottleneck of course but this is what RDBMS are meant to optimize, for ref SQL Server does similar results in less than 30secs. – Arif Eqbal Nov 9 '18 at 13:05
  • pgAdmin reads all data into memory before it begins displaying it; I guess whatever tool you're using with SQL Server is not as stupid. In any case, a plain table scan does not give the database any opportunities for optimization. Is this over a network (where you could monitor network traffic), or on the local machine? – CL. Nov 12 '18 at 9:47

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Browse other questions tagged or ask your own question.