Received: from localhost (maia-1.hub.org [200.46.204.191]) by postgresql.org (Postfix) with ESMTP id 0B4EE9FBF51 for ; Wed, 11 Jul 2007 12:52:34 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.191]) (amavisd-maia, port 10024) with ESMTP id 21506-03 for ; Wed, 11 Jul 2007 12:52:28 -0300 (ADT) X-Greylist: delayed 00:23:40.970594 by SQLgrey-1.7.5 Received: from svr4.postgresql.org (svr4.postgresql.org [66.98.251.159]) by postgresql.org (Postfix) with ESMTP id 1AADB9FBF1E for ; Wed, 11 Jul 2007 12:52:30 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.3 Received: from gslimes3.sigma.fr (mx.sigma.fr [195.25.81.244]) by svr4.postgresql.org (Postfix) with ESMTP id 1307F5AF882 for ; Wed, 11 Jul 2007 15:28:45 +0000 (GMT) Received: from mercure.sigma.fr (mercure.sigma.fr [195.25.81.9]) by gslimes3.sigma.fr (Postfix) with ESMTP id 035D234E53 for ; Wed, 11 Jul 2007 17:28:44 +0200 (CEST) Received: from localhost (localhost.localdomain [127.0.0.1]) by mercure.sigma.fr (Postfix) with ESMTP id E94AE5ED23 for ; Wed, 11 Jul 2007 17:28:43 +0200 (CEST) Received: from mercure.sigma.fr ([127.0.0.1]) by localhost (mercure-1 [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 08156-98 for ; Wed, 11 Jul 2007 17:28:43 +0200 (CEST) Received: from marc (unknown [192.168.1.1]) by mercure.sigma.fr (Postfix) with ESMTP id B8C4F3FDBA for ; Wed, 11 Jul 2007 17:28:43 +0200 (CEST) From: Marc Cousin Organization: Sigma Informatique To: pgsql-performance@postgresql.org Subject: Weird row estimate Date: Wed, 11 Jul 2007 17:28:43 +0200 User-Agent: KMail/1.9.7 MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="Boundary-01=_rcPlGdTN+uAsx/e" Content-Transfer-Encoding: 7bit Content-Disposition: inline Message-Id: <200707111728.43416.mcousin@sigma.fr> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.98 tagged_above=0 required=5 tests=AWL=-1.025, BAYES_50=0.001, HTML_MESSAGE=0.001, TRACKER_ID=2.003 X-Spam-Level: X-Archive-Number: 200707/95 X-Sequence-Number: 25779 --Boundary-01=_rcPlGdTN+uAsx/e Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit 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) --Boundary-01=_rcPlGdTN+uAsx/e Content-Type: text/html; charset="us-ascii" Content-Transfer-Encoding: 7bit

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)

--Boundary-01=_rcPlGdTN+uAsx/e--