Groups > comp.databases.postgresql > #483

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

Path	csiph.com!v102.xanadu-bbs.net!xanadu-bbs.net!eternal-september.org!feeder.eternal-september.org!news.eternal-september.org!.POSTED!not-for-mail
From	Dimitri Fontaine <dimitri@2ndQuadrant.fr>
Newsgroups	comp.databases.postgresql
Subject	Re: Partitioning old tables, partition query planning, optimisation....
Date	Mon, 02 Sep 2013 22:51:01 +0200
Organization	A noiseless patient Spider
Lines	20
Message-ID	<m2vc2jkkwa.fsf@2ndQuadrant.fr> (permalink)
References	<25e9df56-1349-4a83-b002-2c53233ad55a@googlegroups.com>
Mime-Version	1.0
Content-Type	text/plain
Injection-Info	mx05.eternal-september.org; posting-host="8da1d3a971b7bd1218a57efad80890f1"; logging-data="24689"; mail-complaints-to="abuse@eternal-september.org"; posting-account="U2FsdGVkX19SWoHMoeuSL3XlCr0V3g4h"
User-Agent	Gnus/5.130008 (Ma Gnus v0.8) Emacs/24.3 (darwin)
Cancel-Lock	sha1:VY5b8Oj0EnpxS//0awuZR3ddZDY= sha1:Q2Yr118JXv9MG/LGPaR0dW1y24s=
Xref	csiph.com comp.databases.postgresql:483

Show key headers only | 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