Postgres Pro
Downloads
Home   >   mailing lists

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

Pg 8.01 big trouble with LIMIT (bug !?)

From
Hervé Piedvache
Date:
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

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

From
Bruce Momjian
Date:
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

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

From
Michael Fuhr
Date:
On Thu, Feb 24, 2005 at 04:20:03PM -0500, Bruce Momjian wrote:
>
> My guess is that you have not ANALYZEd the tables recently and the
> optimizer is making a bad choice.

I think this problem is similar to one a couple of months ago:

http://archives.postgresql.org/pgsql-bugs/2005-01/msg00174.php

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/