Hi,

I'm having a weird problem on a query :
I've simplified it to get the significant part (see end of message).
The point is I've got a simple
SELECT field FROM table WHERE 'condition1'
Estimated returned rows : 5453
Then
SELECT field FROM table WHERE 'condition2'
Estimated returned rows : 705
Then
SELECT field FROM table WHERE 'condition1' OR 'condition2'
Estimated returned rows : 143998

Condition2 is a bit complicated (it's a subquery).
Nevertheless, shouldn't the third estimate be smaller or equal to the sum of 
the two others ?


Postgresql is 8.2.4 on Linux, stats are up to date,
show default_statistics_target;
 default_statistics_target
---------------------------
 1000



Any ideas ?



explain analyze 
SELECT stc.CMD_ID
         FROM STOL_STC stc
         WHERE  (stc.STC_DATE>='2007-07-05' AND stc.STC_DATEPLAN<='2007-07-05');

                                                    QUERY PLAN
------------------------------------------------------------------------------------------------------------------
 Seq Scan on stol_stc stc  (cost=0.00..24265.15 rows=5453 width=8) (actual 
time=17.186..100.941 rows=721 loops=1)
   Filter: ((stc_date >= '2007-07-05'::date) AND (stc_dateplan <= 
'2007-07-05'::date))
 Total runtime: 101.656 ms
(3 rows)


explain analyze 
SELECT stc.CMD_ID
         FROM STOL_STC stc
         WHERE stc.STC_ID IN 
            (SELECT STC_ID FROM STOL_TRJ 
                    WHERE TRJ_DATEARRT>='2007-07-05' 
                    AND TRJ_DATEDEPT>=TRJ_DATEARRT  
                    AND (TRJ_DATEDEPT<='2007-07-05' 
                    OR TRJ_DATECREAT<='2007-07-05') );


                                                                 QUERY PLAN     
                                                      
--------------------------------------------------------------------------------------------------------------------------------------------
 Nested Loop  (cost=4649.62..10079.52 rows=705 width=8) (actual 
time=6.266..13.037 rows=640 loops=1)
   ->  HashAggregate  (cost=4649.62..4657.13 rows=751 width=8) (actual 
time=6.242..6.975 rows=648 loops=1)
         ->  Index Scan using stol_trj_fk5 on stol_trj  (cost=0.00..4647.61 
rows=803 width=8) (actual time=0.055..4.901 rows=688 loops=1)
               Index Cond: (trj_datearrt >= '2007-07-05'::date)
               Filter: ((trj_datedept >= trj_datearrt) AND ((trj_datedept <= 
'2007-07-05'::date) OR (trj_datecreat <= '2007-07-05'::date)))
   ->  Index Scan using stol_stc_pk on stol_stc stc  (cost=0.00..7.21 rows=1 
width=16) (actual time=0.004..0.005 rows=1 loops=648)
         Index Cond: (stc.stc_id = stol_trj.stc_id)
 Total runtime: 13.765 ms
(8 rows)

explain analyze
SELECT stc.CMD_ID
         FROM STOL_STC stc
         WHERE  (stc.STC_DATE>='2007-07-05' AND stc.STC_DATEPLAN<='2007-07-05')
         OR
            (stc.STC_ID IN 
            (SELECT STC_ID FROM STOL_TRJ 
                    WHERE TRJ_DATEARRT>='2007-07-05' 
                    AND TRJ_DATEDEPT>=TRJ_DATEARRT  
                    AND (TRJ_DATEDEPT<='2007-07-05' 
                    OR TRJ_DATECREAT<='2007-07-05') ));


                                                               QUERY PLAN       
                                                      
----------------------------------------------------------------------------------------------------------------------------------------
 Seq Scan on stol_stc stc  (cost=4649.62..29621.12 rows=143998 width=8) (actual 
time=21.564..146.365 rows=1048 loops=1)
   Filter: (((stc_date >= '2007-07-05'::date) AND (stc_dateplan <= 
'2007-07-05'::date)) OR (hashed subplan))
   SubPlan
     ->  Index Scan using stol_trj_fk5 on stol_trj  (cost=0.00..4647.61 
rows=803 width=8) (actual time=0.054..4.941 rows=688 loops=1)
           Index Cond: (trj_datearrt >= '2007-07-05'::date)
           Filter: ((trj_datedept >= trj_datearrt) AND ((trj_datedept <= 
'2007-07-05'::date) OR (trj_datecreat <= '2007-07-05'::date)))
 Total runtime: 147.407 ms


SELECT count(*) from stol_stc ;
 count
--------
 140960
(1 row)

Reply via email to