pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedQuery slow after VACUUM ANALYZE
3+ messages / 2 participants
[nested] [flat]
* Query slow after VACUUM ANALYZE
@ 2005-08-31 10:22 gdh@eyjar.is
0 siblings, 2 replies; 3+ messages in thread
From: gdh@eyjar.is @ 2005-08-31 10:22 UTC (permalink / raw)
To: pgsql-performance; +Cc: gdh@eyjar.is
Hi all
I'm having a strange problem with a query which looks like this:
SELECT id FROM orders WHERE id NOT IN (SELECT order_id FROM orders_items);
The id fields are varchars (32), both indexed. The number of rows in the
tables are about 60000.
Now, the really strange part is if I delete all data from orders_items,
run VACUUM ANALYZE, then import all the data, the query finshes in about 3
seconds. Then I run VACUUM ANALYZE, and *after* the vacuum, the query
takes
about 30 minutes to run. The data is the same and this is the only query
running, and the machine load is effectively none.
EXPLAIN'ng the query shows, before VACUUM ANALYZE, shows this:
QUERY PLAN
-------------------------------------------------------------------------
Seq Scan on orders (cost=0.00..12184.14 rows=29526 width=33)
Filter: (NOT (hashed subplan))
SubPlan
-> Seq Scan on orders_items (cost=0.00..0.00 rows=1 width=33)
After the vacuum, the plan is like this:
QUERY PLAN
--------------------------------------------------------------------------------
Seq Scan on fsi_orders (cost=0.00..40141767.46 rows=29526 width=33)
Filter: (NOT (subplan))
SubPlan
-> Seq Scan on fsi_orders_items (cost=0.00..1208.12 rows=60412
width=33)
Any ideas what I can do to make the query running in < 10 seconds?
Thanks,
Guðmundur.
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Query slow after VACUUM ANALYZE
@ 2005-08-31 10:26 gdh@eyjar.is
parent: gdh@eyjar.is
1 sibling, 0 replies; 3+ messages in thread
From: gdh@eyjar.is @ 2005-08-31 10:26 UTC (permalink / raw)
To: gdh@eyjar.is; +Cc: pgsql-performance; gdh@eyjar.is
Hi again
[..]
>
> QUERY PLAN
> -------------------------------------------------------------------------
> Seq Scan on orders (cost=0.00..12184.14 rows=29526 width=33)
> Filter: (NOT (hashed subplan))
> SubPlan
> -> Seq Scan on orders_items (cost=0.00..0.00 rows=1 width=33)
>
> After the vacuum, the plan is like this:
>
> QUERY PLAN
> --------------------------------------------------------------------------------
> Seq Scan on fsi_orders (cost=0.00..40141767.46 rows=29526 width=33)
> Filter: (NOT (subplan))
> SubPlan
> -> Seq Scan on fsi_orders_items (cost=0.00..1208.12 rows=60412
> width=33)
>
This, of course, should be "orders", not "fsi_orders", and "orders_items",
not "fsi_orders_items". Sorry for the confusion.
Additional info: I'm running PostgreSQL 7.4.8.
Thanks,
Guðmundur.
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Query slow after VACUUM ANALYZE
@ 2005-09-06 03:53 Tom Lane <tgl@sss.pgh.pa.us>
parent: gdh@eyjar.is
1 sibling, 0 replies; 3+ messages in thread
From: Tom Lane @ 2005-09-06 03:53 UTC (permalink / raw)
To: gdh@eyjar.is; +Cc: pgsql-performance
gdh@eyjar.is writes:
> Any ideas what I can do to make the query running in < 10 seconds?
Increase work_mem (or sort_mem in older releases). PG is dropping
back from the hash plan because it thinks the hashtable won't fit
in work_mem.
regards, tom lane
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2005-09-06 03:53 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-08-31 10:22 Query slow after VACUUM ANALYZE gdh@eyjar.is
2005-08-31 10:26 ` gdh@eyjar.is
2005-09-06 03:53 ` Tom Lane <tgl@sss.pgh.pa.us>
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