performance of IN (subquery)

[Kevin Murphy]

I'm using PG 7.4.3 on Mac OS X.

I am disappointed with the performance of queries like 'select foo from
bar where baz in (subquery)', or updates like 'update bar set foo = 2
where baz in (subquery)'. PG always seems to want to do a sequential
scan of the bar table. I wish there were a way of telling PG, "use the
index on baz in your plan, because I know that the subquery will return
very few results". Where it really matters, I have been constructing
dynamic queries by looping over the values for baz and building a
separate query for each one and combining with a UNION (or just
directly updating, in the update case). Depending on the size of the
bar table, I can get speedups of hundreds or even more than a thousand
times, but it is a big pain to have to do this.

Any tips?

Thanks,
Kevin Murphy

Illustrated:

The query I want to do is very slow:

select bundle_id from build.elements
where elementid in (
SELECT superlocs_2.ele ment_id
FROM superlocs_2 NATURAL JOIN bundle_superloc s_2
WHERE bundle_superloc s_2.protobundle _id = 1);
-----------
7644
7644
(2 rows)
Time: 518.242 ms
The subquery is fast:

SELECT superlocs_2.ele ment_id
FROM superlocs_2 NATURAL JOIN bundle_superloc s_2
WHERE bundle_superloc s_2.protobundle _id = 1;
------------
41209
25047
(2 rows)
Time: 3.268 ms
And using indexes on the main table is fast:

select bundle_id from build.elements
where elementid in (41209, 25047);
-----------
7644
7644
(2 rows)
Time: 2.468 ms

The plan for the slow query:

egenome_test=# explain analyze select bundle_id from build.elements
where elementid in (
SELECT superlocs_2.ele ment_id
FROM superlocs_2 NATURAL JOIN bundle_superloc s_2
WHERE bundle_superloc s_2.protobundle _id = 1);
egenome_test-# egenome_test(# egenome_test(# egenome_test(#
QUERY PLAN
\

------------------------------------------------------------------------
-------------------------------------------------------------
Hash Join (cost=70.33..72 .86 rows=25 width=4) (actual
time=583.051..5 83.059 rows=2 loops=1)
Hash Cond: ("outer".elemen t_id = "inner".element id)
-> HashAggregate (cost=47.83..47 .83 rows=25 width=4) (actual
time=0.656..0.6 58 rows=2 loops=1)
-> Hash Join (cost=22.51..47 .76 rows=25 width=4) (actual
time=0.615..0.6 25 rows=2 loops=1)
Hash Cond: ("outer".superl oc_id = "inner".superlo c_id)
-> Seq Scan on superlocs_2 (cost=0.00..20. 00 rows=1000
width=8) (actual time=0.004..0.0 12 rows=9 loops=1)
-> Hash (cost=22.50..22 .50 rows=5 width=4) (actual
time=0.076..0.0 76 rows=0 loops=1)
-> Seq Scan on bundle_superloc s_2
(cost=0.00..22. 50 rows=5 width=4) (actual time=0.024..0.0 33 rows=2
loops=1)
Filter: (protobundle_id = 1)
-> Hash (cost=20.00..20 .00 rows=1000 width=8) (actual
time=581.802..5 81.802 rows=0 loops=1)
-> Seq Scan on elements (cost=0.00..20. 00 rows=1000 width=8)
(actual time=0.172..405 .243 rows=185535 loops=1)
Total runtime: 593.843 ms
(12 rows)
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

[Tom Lane]

Greg Stark <gs*****@mit.ed u> writes:

It's orthogonal. My point was that I have a bigger problem, but even if I
address it by switching away from plpgsql, or I guess by using EXECUTE, I
would still have a problem. I didn't realize you could run analyze in a
transaction, but even being able to I wouldn't really want to have to do that
repeatedly during the job.
Why not? Given the sampling behavior that's been in there for a release
or two, ANALYZE is pretty cheap on large tables; certainly much cheaper
than any processing you might be doing that's going to grovel over the
whole table.
Except that the first thing the job does is delete all the old records. This
is inside a transaction. So an estimate based on the heap size would be off by
a factor of two by the time the job is done.
Could you use TRUNCATE? I dunno if locking the table is okay for you.
It is transaction safe though.
With analyze in a transaction I'm not clear what the semantics should be
though. I suppose it should only count tuples visible to the transaction
analyze?
It currently uses SnapshotNow, so would see committed tuples of other
transactions plus uncommitted ones of the present transaction. This is
not exactly the same thing as the transaction's snapshot, but close.
A sudden degradation is much more dangerous. Even if it's rare, a sudden
degradation means an outage in prime time.

[ shrug ] You can get a sudden degradation with fixed plans, too. All
it takes is an addition of a lot of rows in some table that had been
small.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

[Gaetano Mendola]

Greg Stark wrote:

Ideally I would want a guarantee that every query would *always* result in the
same plan. Once I've tested them and approved the plans I want to know that
only those approved plans will ever run, and I want to be present and be able
to verify new plans before they go into production.
What you are saying is "never run an ANALYZE" or if you do it you have to re-test
all your plans. "*always*" the same plan is a non sense because the plan depends on the
data distribution, do you test your plans for each given histogram slice ?
I doubt I'm going to convince anyone today...

For sure not me.
Regards
Gaetano Mendola

[Greg Stark]

Tom Lane <tg*@sss.pgh.pa .us> writes:

Except that the first thing the job does is delete all the old records. This
is inside a transaction. So an estimate based on the heap size would be off by
a factor of two by the time the job is done.
Could you use TRUNCATE? I dunno if locking the table is okay for you.
It is transaction safe though.

Well, if necessary I could, but if I can do it without downtime all the
better. In any case I think I'll be ok with a factor of 2 misestimation. I was
just giving an example use case for you to chew on when analyzing this new
proposal.

I'm not sure where I stand with the idea. I like the idea that table sizes
would always be fairly reasonable even without statistics. But I also have a
really strong desire for plan stability.
[ shrug ] You can get a sudden degradation with fixed plans, too. All
it takes is an addition of a lot of rows in some table that had been
small.

Well, presumably I should be aware if my data distribution is changing
drastically. That's under my control. At least the performance change will be
proportionate to the distribution change.

With plans changing on the fly I could have a query that degrades 1% for every
row added and then suddenly becomes 10x slower when I add a 17th extra row. Of
course such a system isn't perfectly tuned, or the optimizer issue should be
found and fixed. But I would rather find out about it without having my
application fail.

--
greg
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org