Pg 8.01 big trouble with LIMIT (bug !?)

From: Hervé Piedvache <herve(at)elma(dot)fr>
To: pgsql-general(at)postgresql(dot)org
Subject: Pg 8.01 big trouble with LIMIT (bug !?)
Date: 2005-02-22 14:28:32
Message-ID: 200502221528.32418.herve@elma.fr
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Bruno Wolff III 2005-02-22 14:38:01 Re: Off-topic: monograph for database postgraduate
Previous Message Brar Piening 2005-02-22 14:23:23 Is there a way to implicitly grant privileges to an implicitly created sequence?