From: Edin Kadribasic <edink@emini.dk>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: John A Meinel <john@arbash-meinel.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Optimizer wrongly picks Nested Loop Left Join
Date: Thu, 12 May 2005 01:28:25 +0200
Message-ID: <003001c55681$216ba5c0$0b00000a@blaster> (raw)
References: <001f01c55604$494d2e60$1200000a@intra.emini.dk>
<3742.1115821114@sss.pgh.pa.us>
From: "Tom Lane" <tgl@sss.pgh.pa.us>
> "Edin Kadribasic" <edink@emini.dk> writes:
> > I have a query that is giving the optimizer (and me) great headache.
>
> The main problem seems to be that the rowcount estimates for
> axp_temp_order_match and axp_dayschedule are way off:
>
> > -> Index Scan using axp_temp_order_match_idx1 on
> > axp_temp_order_match a (cost=0.00..209.65 rows=426 width=4) (actual
> > time=0.277..0.512 rows=6 loops=1)
> > Index Cond: (sid = 16072)
>
> > -> Index Scan using axp_dayschedule_day_idx
on
> > axp_dayschedule ds (cost=0.00..3.02 rows=1 width=8) (actual
> > time=0.036..3.973 rows=610 loops=1)
> > Index Cond: (("day" >=
'2005-05-12'::date)
> > AND ("day" <= '2005-05-12'::date))
> > Filter: (used = B'1'::"bit")
>
> > -> Index Scan using axp_temp_order_match_idx1 on
> > axp_temp_order_match a (cost=0.00..2.45 rows=1 width=4) (actual
> > time=0.027..2.980 rows=471 loops=1)
> > Index Cond: (sid = 16092)
>
> > -> Index Scan using axp_dayschedule_day_idx on
> > axp_dayschedule ds (cost=0.00..3.02 rows=1 width=8) (actual
> > time=0.015..3.557 rows=606 loops=471)
> > Index Cond: (("day" >= '2005-05-13'::date)
AND
> > ("day" <= '2005-05-13'::date))
> > Filter: (used = B'1'::"bit")
>
> Do you ANALYZE these tables on a regular basis? If so, it may be
> necessary to increase the statistics target to the point where you
> get better estimates.
Increasing statistics didn't seem to help, but both of you gave me an idea
of what might be wrong. axp_temp_order match contains temporary matches for
a search. Just before execution of that query the new matches are inserted
into the table under a new search id (sid column). Since the ANALYZE was
that before it it grossly underestimates the number of matches for that sid.
As this table is relatively small inserting ANALYZE axp_temp_order_match
just before running the query does not introduce a great perforance penalty
(50ms) and it reduces the query execution time from up to 50s down to ~20ms.
> > Please note that sometimes when I get "bad plan" in the logfile, I just
> > re-run the query and the optimizer chooses the more efficient one.
>
> That's fairly hard to believe, unless you've got autovacuum running
> in the background.
The application had ANALYZE axp_temp_order_match placed in the "slightly"
wrong location, before the large insert was done (1000 rows with a new
sid). So when the app run the next search, previous search got correctly
analyzed and the query execution time dropped dramatically as I was trying
to EXPLAIN ANALYZE query recorded in the log file.
Thanks for your help,
Edin
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: edink@emini.dk, tgl@sss.pgh.pa.us, john@arbash-meinel.com
Subject: Re: Optimizer wrongly picks Nested Loop Left Join
In-Reply-To: <003001c55681$216ba5c0$0b00000a@blaster>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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