pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Tom Mack <pgsql@tom-mack.com>
To: pgsql-performance@postgresql.org
Cc: twanger@bluetwanger.de
Subject: Problem with slow query (caused by improper nestloop?)
Date: Wed, 13 Apr 2005 16:06:57 -0500
Message-ID: <425D89F1.4040907@tom-mack.com> (raw)

Someone (twanger) sent me here from the IRC channel with the following:

I have a query that normally takes 0.150 seconds, but after an insert 
can take 14 seconds.

Here's the scenario:

Run this query:
       select *
       from cad_part
         left join smart_part using (cannon_part_id)
       where cad_import_id = 91
       order by cad_part_reference_letter, cad_part_id

The result is returned in about 150ms.

Then I run my import operation which adds 1 new cad_import row, about 30 
new cad_part rows, and about 100 new cad_line rows (which aren't 
involved in the above query). In this case, the new cad_import row has a 
PK of cad_import_id = 92.

When I run the query again (only the where clause changed):
       select *
       from cad_part
         left join smart_part using (cannon_part_id)
       where cad_import_id = 92
       order by cad_part_reference_letter, cad_part_id

it takes about 14 seconds (and has a different plan).

I can repeat the first query (id=91) and it still executes in 150ms and 
then repeat the second query and in still takes ~14 seconds. 

I've found two things that fix this. First, if I run analyze, the second 
query will take 150ms.

Second, if I set enable_nestloop to false the second query will use that 
same plan that the first does and complete in 150ms.

I've posted a bunch of details on my website including the size of the 
tables (all pretty small), both query plans, and some of the schema.

http://tom-mack.com/query_details.html

I also just redid the query without the final order by clause with the 
same results.

So I guess my question is, am I doing something wrong? did I miss an 
index or something? is this a bug (a 100x hit for not running analyze 
seems a little severe)? should I just run "analyze cad_part" after my 
inserts to that table?

Thanks,

--Tom





Message-ID: <425D89F1.4040907@tom-mack.com>
Permalink:  ../425D89F1.4040907@tom-mack.com/
Also on:    postgresql.org/message-id/425D89F1.4040907@tom-mack.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 using the --to and --cc options:
  reply via email

  To: pgsql-performance@postgresql.org
  Cc: pgsql@tom-mack.com, twanger@bluetwanger.de
  Subject: Re: Problem with slow query (caused by improper nestloop?)
  In-Reply-To: <425D89F1.4040907@tom-mack.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