From: Richard Huxton <dev@archonet.com>
To: Віталій Тимчишин <tivv00@gmail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: PostgreSQL OR performance
Date: Fri, 07 Nov 2008 10:45:50 +0000
Message-ID: <49141C5E.2010404@archonet.com> (raw)
In-Reply-To: <331e40660811070114rd70cf0dy194020a9f9ea8802@mail.gmail.com>
References: <331e40660811050312u552cab53ifa88e493e1c4f7ac@mail.gmail.com>
<1225902700.1432.53.camel@jdavis>
<331e40660811060246m51df553egd4e2ba91f08d1434@mail.gmail.com>
<29e3942f0811060426k5fe34b1av401a2dba62e4ae3c@mail.gmail.com>
<331e40660811060833l49bf837em41ca88d4b2061ea1@mail.gmail.com>
<49131ED5.80307@archonet.com>
<331e40660811060937q460e6f9fvf336455200d31f56@mail.gmail.com>
<331e40660811070114rd70cf0dy194020a9f9ea8802@mail.gmail.com>
Віталій Тимчишин wrote:
> I am sorry, I've emptied atom_match table, so one part produce 0 result, but
> anyway here is explain:
David's right - the total estimate is horribly wrong
> "Merge Join (cost=518771.07..62884559.80 rows=1386158171 width=32) (actual
> time=30292.802..755751.242 rows=34749 loops=1)"
But it's this materialize that's taking the biggest piece of the time.
> " -> Materialize (cost=469981.13..498937.42 rows=2316503 width=30)
> (actual time=15915.639..391938.338 rows=242752539 loops=1)"
15.9 seconds to 391.9 seconds. That's half your time right there. The
fact that it's ending up with 242 million rows isn't promising - are you
sure the query is doing what you think it is?
> " -> Sort (cost=469981.13..475772.39 rows=2316503 width=30) (actual
> time=15915.599..19920.912 rows=2316503 loops=1)"
> " Sort Key: production.company.run_id"
> " Sort Method: external merge Disk: 104896kB"
By constrast, this on-disk sort of 104MB is comparatively fast.
> P.S. May be I've chosen wrong list and my Q better belongs to -hackers?
No - hackers is if you want to discuss the code of the database server
itself.
--
Richard Huxton
Archonet Ltd
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 using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: dev@archonet.com, tivv00@gmail.com
Subject: Re: PostgreSQL OR performance
In-Reply-To: <49141C5E.2010404@archonet.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