pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Aaron Guyon <battlemage@gmail.com>
To: pgsql-performance@postgresql.org
Subject: Re: Postgres 8.3, four times slower queries?
Date: Thu, 5 Mar 2009 14:58:09 -0500
Message-ID: <f0afbb9b0903051158s3e0a9f69re04077f8f43b5ec3@mail.gmail.com> (raw)
In-Reply-To: <1236275907.6979.40.camel@jd-laptop.pragmaticzealot.org>
References: <f0afbb9b0903021618w616f1158t84dbf119f0b72c70@mail.gmail.com>
	<2870.1236050632@sss.pgh.pa.us>
	<f0afbb9b0903030931g7b219137n2954cf058d523e52@mail.gmail.com>
	<1236275907.6979.40.camel@jd-laptop.pragmaticzealot.org>

On Thu, Mar 5, 2009 at 12:58 PM, Joshua D. Drake <jd@commandprompt.com>wrote:

> What happens if you do this:
>
> SET cpu_tuple_cost TO '0.5';
> SET cpu_index_tuple_cost TO '0.5';
> EXPLAIN ANALYZE 8.3 query....
>

Right now, I'm getting very good results with the above.  I'm still running
additional tests but I'll keep you guys updated. I've attached the new
explain analyze.

Attachments:

new_tuple1.txt (text/plain, 6.1K) download SET SET QUERY PLAN --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Unique (cost=17397.17..17397.18 rows=2 width=20) (actual time=621.865..621.865 rows=0 loops=1) -> Sort (cost=17397.17..17397.18 rows=2 width=20) (actual time=621.857..621.857 rows=0 loops=1) Sort Key: t8.id Sort Method: quicksort Memory: 17kB -> Nested Loop (cost=0.00..17397.16 rows=2 width=20) (actual time=621.825..621.825 rows=0 loops=1) Join Filter: ((t2.not_modified_since >= to_timestamp('2009-02-24T11:19:57'::text, 'YYYY/MM/DD HH:MI:SS'::text)) OR (t3.not_modified_since >= to_timestamp('2009-02-24T11:19:57'::text, 'YYYY/MM/DD HH:MI:SS'::text)) OR (t6.not_modified_since >= to_timestamp('2009-02-24T11:19:57'::text, 'YYYY/MM/DD HH:MI:SS'::text)) OR (t7.not_modified_since >= to_timestamp('2009-02-24T11:19:57'::text, 'YYYY/MM/DD HH:MI:SS'::text)) OR (t8.not_modified_since >= to_timestamp('2009-02-24T11:19:57'::text, 'YYYY/MM/DD HH:MI:SS'::text)) OR (t9.not_modified_since >= to_timestamp('2009-02-24T11:19:57'::text, 'YYYY/MM/DD HH:MI:SS'::text)) OR (t10.not_modified_since >= to_timestamp('2009-02-24T11:19:57'::text, 'YYYY/MM/DD HH:MI:SS'::text)) OR (t11.not_modified_since >= to_timestamp('2009-02-24T11:19:57'::text, 'YYYY/MM/DD HH:MI:SS'::text))) -> Nested Loop (cost=0.00..14615.82 rows=537 width=76) (actual time=51.092..556.405 rows=1104 loops=1) -> Nested Loop (cost=0.00..12007.84 rows=541 width=52) (actual time=46.530..523.869 rows=1104 loops=1) -> Nested Loop (cost=0.00..2229.06 rows=147 width=44) (actual time=42.642..501.168 rows=332 loops=1) -> Nested Loop (cost=0.00..1530.56 rows=114 width=40) (actual time=39.485..489.224 rows=336 loops=1) -> Nested Loop (cost=0.00..757.31 rows=177 width=28) (actual time=10.877..473.774 rows=376 loops=1) -> Nested Loop (cost=0.00..69.07 rows=4 width=20) (actual time=6.463..6.640 rows=4 loops=1) -> Index Scan using idx_department_du on department t10 (cost=0.00..14.72 rows=7 width=12) (actual time=6.141..6.173 rows=7 loops=1) Index Cond: (company_id = 250893) Filter: (active <> 0) -> Index Scan using idx_project_department_id on project t2 (cost=0.00..7.26 rows=1 width=16) (actual time=0.051..0.053 rows=1 loops=7) Index Cond: (t2.department_id = t10.id) Filter: (t2.active <> 0) -> Index Scan using idx_project_type_project_id on project_type t11 (cost=0.00..133.37 rows=77 width=16) (actual time=1.112..116.094 rows=94 loops=4) Index Cond: (t11.project_id = t2.id) Filter: (t11.active <> 0) -> Index Scan using project_invoice_pkey on project_invoice t3 (cost=0.00..3.87 rows=1 width=12) (actual time=0.024..0.027 rows=1 loops=376) Index Cond: (t3.id = t11.slot_id) Filter: (t3.active <> 0) -> Index Scan using idx_invoice_owner_resource_id on invoice t7 (cost=0.00..5.62 rows=1 width=16) (actual time=0.017..0.021 rows=1 loops=336) Index Cond: (t7.owner_resource_id = t3.id) Filter: (t7.active <> 0) -> Index Scan using idx_payment_invoice_id on payment t6 (cost=0.00..52.45 rows=28 width=16) (actual time=0.021..0.037 rows=3 loops=332) Index Cond: (t6.invoice_id = t7.id) Filter: (t6.active <> 0) -> Index Scan using idx_payment_amount_payment_id on payment_amount t8 (cost=0.00..4.32 rows=1 width=28) (actual time=0.011..0.015 rows=1 loops=1104) Index Cond: (t8.payment_id = t6.id) -> Index Scan using amount_pkey on amount t9 (cost=0.00..4.64 rows=1 width=12) (actual time=0.012..0.016 rows=1 loops=1104) Index Cond: (t9.id = t8.amount_id) Total runtime: 622.377 ms (35 rows)

view thread (16+ messages)  latest in thread

Message-ID: <f0afbb9b0903051158s3e0a9f69re04077f8f43b5ec3@mail.gmail.com>
Permalink:  ../f0afbb9b0903051158s3e0a9f69re04077f8f43b5ec3@mail.gmail.com/
Also on:    postgresql.org/message-id/f0afbb9b0903051158s3e0a9f69re04077f8f43b5ec3@mail.gmail.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-performance@postgresql.org
  Cc: battlemage@gmail.com
  Subject: Re: Postgres 8.3, four times slower queries?
  In-Reply-To: <f0afbb9b0903051158s3e0a9f69re04077f8f43b5ec3@mail.gmail.com>
  References: <f0afbb9b0903021618w616f1158t84dbf119f0b72c70@mail.gmail.com> <2870.1236050632@sss.pgh.pa.us> <f0afbb9b0903030931g7b219137n2954cf058d523e52@mail.gmail.com> <1236275907.6979.40.camel@jd-laptop.pragmaticzealot.org> <f0afbb9b0903051158s3e0a9f69re04077f8f43b5ec3@mail.gmail.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox