Re: Pg 8.01 big trouble with LIMIT (bug !?) - Mailing list pgsql-general

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@postgresql.org so that your
>       message can get through to the mailing list cleanly
>

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