From: | "Webb Sprague" <webb(dot)sprague(at)gmail(dot)com> |
---|---|
To: | |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Database slowness -- my design, hardware, or both? |
Date: | 2007-03-07 21:44:51 |
Message-ID: | b11ea23c0703071344o3ce0598fnf1294bf1e0f7a7ca@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
> OK, I modified things to use interpolation. Here's the updated query:
>
>
> explain UPDATE Transactions
> SET previous_value = previous_value(id)
> WHERE new_value IS NOT NULL
> AND new_value <> ''
> AND node_id IN (351, 169, 664, 240);
>
> And here is the query plan produced by explain:
>
> QUERY
> PLAN
> ----------------------------------------------------------------------------------
> Bitmap Heap Scan on transactions (cost=8842.88..98283.93 rows=407288
> width=249)
> Recheck Cond: (node_id = ANY
> ('{351,169,664,240}'::integer[]))
> Filter: ((new_value IS NOT NULL) AND (new_value <> ''::text))
> -> Bitmap Index Scan on node_id_idx (cost=0.00..8842.88 rows=434276
> width=0)
> Index Cond: (node_id = ANY
> ('{351,169,664,240}'::integer[]))
> (5 rows)
> I'm still a bit surprised by how different the query plan came out with
> what would seem like a minor change.
Do you have new \timings?
What you or I think is a minor change isn't necessarily what the
planner thinks is a minor change, especially when you change data from
something that requires a query to something that is determinate. I
would suggest changing your function to remove as many such queries as
possible too (I am thinking of the order by limit 1). This would be a
good move also in that you narrow down the amount of moving parts to
diagnose and it just makes the whole thing cleaner.
The meta-moral is that db optimization requires systematic
experimentation. Use the database to store the results of the various
experiments!
In light of this, I would suggest you try removing the check clauses
and seeing if you get a difference too. Just like Francis Bacon said
-- don't deduce from first principles, experiment!
I would also try amortizing the analysis with triggers, rather than
building the table all at once; this may be better or worse, depending
on the on-line character of the application (e.g., if they are waiting
at an ATM, in might be a deal breaker to add two seconds to do an
insert / update, but not if you are tailing off a log file that gets
updated every minute or so.)
W
From | Date | Subject | |
---|---|---|---|
Next Message | Simon Riggs | 2007-03-07 22:03:16 | Re: [HACKERS] WITH/RECURSIVE plans |
Previous Message | Anton Melser | 2007-03-07 21:26:18 | Re: pg temp tables |