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.

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?



---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Reply via email to