Re: Pg 8.01 big trouble with LIMIT (bug !?)

My guess is that you have not ANALYZEd the tables recently and the
optimizer is making a bad choice.

---------------------------------------------------------------------------

Herv Piedvache wrote:
> Hi,
>
> I have a "simple" request without a limit giving me this :
>
> # select a.id_my from sites_articles a, site_my s where s.id_site = a.id_site
> and s.language = 'aa' and s.id_category = 11 order by my_date desc ;
> id_my
> --------
> (0 rows)
>
> Time: 3.537 ms
>
> If I put a LIMIT (because it'll be an automatic script with many categories
> values to test ... so I can get sometime no result, so other time some result
> depending of the category to test, so I need a LIMIT ... not for stupidity
> only !)
>
> # select a.id_my from sites_articles a, site_my s where s.id_site = a.id_site
> and s.language = 'aa' and s.id_category = 11 order by my_date desc limit 50;
>
> Then I get no result after more than 10 minutes of waiting ... I cancel the
> request :o((
>
> How it could be possible to get no result (or so many time) for the same
> request with 3.537 ms without LIMIT just by adding the LIMIT at the end of my
> request !?
>
> This is the explains ...
>
> # explain select a.id_my from sites_articles a, site_my s where s.id_site =
> a.id_site and s.language = 'aa' and s.id_category = 11 order by my_date
> desc ;
>
> QUERY PLAN
> -------------------------------------------------------------------------------------------------------------
> Sort (cost=384658.32..384816.96 rows=63455 width=12)
> Sort Key: a.my_date
> -> Hash Join (cost=730.42..379596.69 rows=63455 width=12)
> Hash Cond: ("outer".id_site = "inner".id_site)
> -> Seq Scan on sites_articles a (cost=0.00..289779.48 rows=17690448
> width=16)
> -> Hash (cost=728.56..728.56 rows=743 width=4)
> -> Index Scan using ix_site_my_language on site_my s
> (cost=0.00..728.56 rows=743 width=4)
> Index Cond: ("language" = 'aa'::text)
> Filter: (id_category = 11)
> (9 rows)
>
> Time: 1.054 ms
>
> # explain select a.id_my from sites_articles a, site_my s where s.id_site =
> a.id_site and s.language = 'aa' and s.id_category = 11 order by my_date desc
> limit 50;
> QUERY PLAN
> -------------------------------------------------------------------------------------------------------------------------------------
> Limit (cost=0.00..56486.93 rows=50 width=12)
> -> Nested Loop (cost=0.00..71687563.58 rows=63455 width=12)
> -> Index Scan Backward using ix_sites_articles_my_date on
> sites_articles a (cost=0.00..401316.25 rows=17690448 width=16)
> -> Index Scan using site_my_id_site_key on site_my s
> (cost=0.00..4.02 rows=1 width=4)
> Index Cond: (s.id_site = "outer".id_site)
> Filter: (("language" = 'aa'::text) AND (id_category = 11))
> (6 rows)
>
> Time: 1.020 ms
>
> Thanks per advance for your ideas ...
>
> Regards,
> --
> Herv? Piedvache
>
> Elma Ing?nierie Informatique
> 6 rue du Faubourg Saint-Honor?
> F-75008 - Paris - France
> Pho. 33-144949901
> Fax. 33-144949902
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo(at)postgresql(dot)org so that your
> message can get through to the mailing list cleanly
>

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073