Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.databases.postgresql > #483

Re: Partitioning old tables, partition query planning, optimisation....

Show all headers | View raw

steve.pike@carbonculture.net writes:
> The "problematic" part comes when requesting say, a year's worth of data
> over more than a few of the fk ids (for example, a year's worth over a range
> of 150 fk ids)... queries run to 10 minutes.

If you want to be optimizing, you need to first profile. In PostgreSQL
that is done with using the EXPLAIN statement:

  EXPLAIN ANALYZE <query here>;
  EXPLAIN (ANALYZE, VERBOSE, BUFFERS) <query here>;

The second variant is the one to prefer, but only works with recent
enough versions of PostgreSQL. Then you need to learn how to read the
explain plan, go ask for help on the PostgreSQL mailing lists, either
general or performances, see http://www.postgresql.org/list/

Regards,
-- 
Dimitri Fontaine
PostgreSQL DBA, Architecte

Back to comp.databases.postgresql | Previous | Next — Previous in thread | Find similar | Unroll thread

Thread

Partitioning old tables, partition query planning, optimisation.... steve.pike@carbonculture.net - 2013-08-30 06:45 -0700
  Re: Partitioning old tables, partition query planning, optimisation.... Dimitri Fontaine <dimitri@2ndQuadrant.fr> - 2013-09-02 22:51 +0200

csiph-web