pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Query slow after VACUUM ANALYZE
3+ messages / 2 participants
[nested] [flat]

* Query slow after VACUUM ANALYZE
@ 2005-08-31 10:22 gdh@eyjar.is
  2005-08-31 10:26 ` Re: Query slow after VACUUM ANALYZE gdh@eyjar.is
  2005-09-06 03:53 ` Re: Query slow after VACUUM ANALYZE Tom Lane <tgl@sss.pgh.pa.us>
  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:22 Query slow after VACUUM ANALYZE gdh@eyjar.is
@ 2005-08-31 10:26 ` 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-08-31 10:22 Query slow after VACUUM ANALYZE gdh@eyjar.is
@ 2005-09-06 03:53 ` Tom Lane <tgl@sss.pgh.pa.us>
  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