pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Jacek Rembisz <jr178783@zodiac.mimuw.edu.pl>
To: pgsql-performance@postgresql.org
Subject: Re: Analyze makes queries slow...
Date: Tue, 12 Aug 2003 11:57:07 +0200
Message-ID: <20030812095707.GA22395@melina.szuwary.org> (raw)
In-Reply-To: <20030811155841.483ffd93.svb@ucs.co.za>
References: <20030811155841.483ffd93.svb@ucs.co.za>

On Mon, Aug 11, 2003 at 03:58:41PM +0200, Stef wrote:

> I have attached an sql statement that normally runs under 3 minutes.
> That is, until I vacuum analyze the database (or just the tables in the query),
> then the same query runs longer than 12 hours, and I have to kill it.

Hmm, I have noticed similar problem with a query with order by ... limit clause.Although it runs only 10 times slower after analyze :)

The query joins one big table (20 000 rows) with several small tables
(200-4000 rows) than order by "primary key of big table" limit 20

Without this order by ... limit clause the query is 5 times faster after
analyze.

Looking into explain analyze outputs:
1. Before vacuum analyze  a planer chooses nested loop, the  deepest is:
  ->  Nested Loop  (cost=0.00..116866.54 rows=19286 width=96) (actual time=0.14..1.39 rows=21 loops=1)
      ->  Index Scan Backward using big_table_pkey on big_table k  (cost=0.00..1461.15 rows=19286 width=52) (actual time=0.07..0.47 rows=21 loops=1)
      ->  Index Scan using 4000rows_table_pkey on 4000rows_table zs  (cost=0.00..5.97 rows=1 width=44) (actual time=0.02..0.02 rows=0 loops=21)

2. After analyze uses hashjoins

When I remove this order by limit clause the query after analyze takes 
the same time and the query before analyze is much more slower.

I won't blame the planer. How he could learn that he should first 
take those 20 rows and than perform joins? There is a where clause
with complex exists(subquery) condition regarding one of big_table fields,
but removing this condition does not change the query plan.

Pure joining without any additional conditions and only primary key of big 
table in select clause runs 4 times slower then whole query before 
vacuuum analyze :)

Does in all the planer take in the consideration the limit clause?

Probably I'm missing something. I don't know much about the planer.

Finaly I have redesigned the query.

Regards,
Jacek




view thread (7+ messages)  latest in thread

Message-ID: <20030812095707.GA22395@melina.szuwary.org>
Permalink:  ../20030812095707.GA22395@melina.szuwary.org/
Also on:    postgresql.org/message-id/20030812095707.GA22395@melina.szuwary.org

 · 

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: jr178783@zodiac.mimuw.edu.pl
  Subject: Re: Analyze makes queries slow...
  In-Reply-To: <20030812095707.GA22395@melina.szuwary.org>
  References: <20030811155841.483ffd93.svb@ucs.co.za> <20030812095707.GA22395@melina.szuwary.org>

* 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