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