Re: Benchmarking

Try using the explain command. For example here are two queries that
give me the same result:

1. SELECT max(dt) FROM caseweights1;

2. SELECT dt FROM caseweights1 ORDER BY dt DESC limit 1;

However, these two queries generate wildly different query plans:

1st query plan

processdata=# EXPLAIN SELECT max(dt) FROM caseweights1;
NOTICE: QUERY PLAN:

Aggregate (cost=30108.49..30108.49 rows=1 width=8)
-> Seq Scan on caseweights1 (cost=0.00..26013.79 rows=1637879 width=8)

EXPLAIN

2nd query plan

processdata=# EXPLAIN SELECT * FROM caseweights1 ORDER BY dt DESC limit 1;
NOTICE: QUERY PLAN:

Limit (cost=0.00..1.61 rows=10 width=12)
-> Index Scan Backward using caseweights1_dt_idx on caseweights1 (cost=0.00..264003.33 rows=1637879 width=12)

EXPLAIN

And the second returns its result in much less time (it's basically
instantaneous while the first one takes nearly a minute on my test
server.

Now, this is a contrived example, but it is a good example of one of
the more useful PostgreSQL tricks I know. It also clearly
demonstrates how EXPLAIN works. One of the more useful things about
the PostgreSQL mailing list is that it is inhabited by folks that
actually undertand these query plans (even the really crazy ones).

Good Luck,

Jason

Francisco Reyes <lists(at)natserv(dot)com> writes:

> I have been reading about performance tuning and plan to try some
> suggestions I have found through the archive.
>
> How can I benchmark a query to see if it improves after I make some
> changes other than just try and time it?
>
>
> Is there any way to have some stats appear after a query is done on psql?
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo(at)postgresql(dot)org