Hello and welcome. Today we're focusing on what it takes to better tune our queries in PostgreSQL. For those who have been following this series, you will know that we have seen some in depth ways we can analyze and understand what is going on within our queries. Now we're going to focus on a few things we can do to make them a little more efficient. Let's get started.

First we need to open PSequel, create our tables, then populate them with some dummy data. Let's get this handled.

CREATE TABLE item ( id INTEGER, description VARCHAR (255) ); CREATE TABLE garbage ( id INTEGER, garbage VARCHAR (255) ); INSERT INTO item VALUES (generate_series(1,10000), md5(random()::text)); INSERT INTO garbage VALUES (generate_series(1,10000), md5(random()::text));

Excellent, now let's run a basic query then analyze what is going on.

EXPLAIN ANALYZE SELECT * FROM item WHERE id > 35 AND id < 4537;
Seq Scan on item (cost=0.00..234.00 rows=4503 width=37) (actual...