PostgreSQL: Query speed up using WITH

In this post I wan’t to show you how can be important to make a rigth constructed select.
We have a three tables:
points, exchange_rates and profiles;
with indexes on exchange_rates.profiles__id, points.brands__id
but this not so important for current theme and no one of them not fired at execution time.

Table points have a 7973 records, profiles – 10, exchange_rates – 120.

Let’s look at two select with different cunstruction:

basename=# explain (analyze, verbose) SELECT "points"."cities__id", MAX("exchange_rates"."buy_rate") FROM "exchange_rates", "profiles", "points" WHERE "exchange_rates"."current" AND "exchange_rates"."profiles__id" = "profiles"."id" AND "profiles"."brands__id" = "points"."brands__id" AND "exchange_rates"."range_above" = 0 GROUP BY "points"."cities__id";
Total runtime: 12.464 ms (12.464 ms ... 19.823 ms) (23 rows)

and

basename=# explain (analyze, verbose) WITH erp AS ( SELECT DISTINCT brands__id, profiles__id, buy_rate FROM exchange_rates er LEFT JOIN profiles p ON p.id = er.profiles__id WHERE current AND er.range_above=0 ), p AS ( SELECT DISTINCT p.cities__id, p.brands__id FROM points p ) SELECT p.cities__id, MAX(erp.buy_rate) FROM p LEFT JOIN erp ON erp.brands__id = p.brands__id WHERE p.cities__id IS NOT NULL AND p.brands__id IS NOT NULL GROUP BY p.cities__id;
Total runtime: 9.123 ms (7.704 ms ... 10.886 ms) (34 rows)
Now let’s view inside: "WITH provides a way to write auxiliary statements for use in a larger query.
These statements, which are often referred to as Common Table Expressions or CTEs,
can be thought of as defining temporary tables that exist just for one query.
Each auxiliary statement in a WITH clause can be a SELECT, INSERT, UPDATE, or
DELETE; and the WITH clause itself is attached to a primary statement that can
also be a SELECT, INSERT, UPDATE, or DELETE"
http://www.postgresql.org/docs/9.4/static/queries-with.html

And by my things about this:
Also minimization as it possible rows count in both side of joins tables can cost smaller than it can be on direct query using a “while”.


Leave a Reply

Your email address will not be published. Required fields are marked *