I have a simple SQL query: SELECT * FROM table; - a simple sequential scan - which takes me 10s.
When I add CREATE TABLE AS (CTAS syntax) to it, i.e. my query is CREATE TABLE db_test AS SELECT * FROM table;, the query now takes 18s.
I could not see any notable difference between the plan.
What are the steps I can take to minimize this 8s overhead? I have already tried using UNLOGGED keyword.
I could not see any notable difference between the plan.
That's because there isn't any.
Table Scanning is Table Scanning. As Lewis Carroll's King of Wonderland said:
Begin at the beginning and go on till you come to the end: then stop.
The difference is in what's being done with the data:
... takes 10 seconds.
... takes 18 seconds.
Spotted the difference?
Reading stuff in a database is [usually] quick. It's often just floating around in the Buffer Cache and can be quickly sent on its way.
Writing stuff in a database is slower. It [usually] requires logging into the Transaction log first, then writing to the Buffer Cache and flushing out to disk (at some point). It all takes time.
Consider:
Why are you creating a copy of the table at all? If the second table contains everything in the first one, then why not just use the first one?
Do you really want all the rows? If you're reading everything, then there's not much you can do to speed things up. No amount of indexing can speed up a sequential Table Scan.
Do you really want all the columns? If you're only interested in three, little, columns but are retrieving a hundred columns, full of massive BLOB data, then things will get a bit slow.
