Skip site navigation (1) Skip section navigation (2)

Incorrect behavior with CE and ORDER BY

From: "Joshua D(dot) Drake" <jd(at)commandprompt(dot)com>
To: PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org>
Subject: Incorrect behavior with CE and ORDER BY
Date: 2006-10-24 16:33:21
Message-ID: 453E4051.9040700@commandprompt.com (view raw or flat)
Thread:
Lists: pgsql-hackers
Hello,

We have a problem with CE that I want to verify is either expected
behavior, a bug or something else :).

Yes constraint exclusion is on.

I have tried increasing the default_statistics_target (all the way 1000)
no change in behavior.

Query plan with ORDER BY:

 Limit  (cost=47110.19..47110.31 rows=50 width=8) (actual
time=6088.013..6088.269 rows=50 loops=1)
   ->  Sort  (cost=47110.19..47943.14 rows=333179 width=8) (actual
time=6088.007..6088.104 rows=50 loops=1)
         Sort Key: public.tbl_profile_search.pse_lastlogin
         ->  Result  (cost=0.00..16547.78 rows=333179 width=8) (actual
time=0.020..4339.472 rows=334319 loops=1)
               ->  Append  (cost=0.00..16547.78 rows=333179 width=8)
(actual time=0.016..3208.022 rows=334319 loops=1)
                     ->  Seq Scan on tbl_profile_search
(cost=0.00..2.27 rows=1 width=8) (actual time=0.012..0.047 rows=2 loops=1)
                           Filter: (((pse_normalized_text)::text =
'1'::text) AND (pse_interest_type = 10))
                     ->  Index Scan using index_pse_09_on_part_1 on
tbl_profile_search_interest_1 tbl_profile_search  (cost=0.00..4.73
rows=1 width=8) (actual time=0.202..0.202 rows=0 loops=1)
                           Index Cond: ((pse_normalized_text)::text =
'1'::text)
                           Filter: (pse_interest_type = 10)
                     ->  Bitmap Heap Scan on
tbl_profile_search_interest_10 tbl_profile_search
(cost=3579.12..16540.78 rows=333177 width=8) (actual
time=90.619..2116.224 rows=334317 loops=1)
                           Recheck Cond: ((pse_normalized_text)::text =
'1'::text)
                           Filter: (pse_interest_type = 10)
                           ->  Bitmap Index Scan on
index_pse_09_on_part_10  (cost=0.00..3579.12 rows=333177 width=0)
(actual time=89.052..89.052 rows=340964 loops=1)
                                 Index Cond:
((pse_normalized_text)::text = '1'::text)
 Total runtime: 6103.190 ms


Same query, just removed ORDER BY:


-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.00..2.48 rows=50 width=4) (actual time=0.025..57.146
rows=50 loops=1)
   ->  Result  (cost=0.00..16549.78 rows=333179 width=4) (actual
time=0.021..56.993 rows=50 loops=1)
         ->  Append  (cost=0.00..16549.78 rows=333179 width=4) (actual
time=0.017..56.835 rows=50 loops=1)
               ->  Seq Scan on tbl_profile_search  (cost=0.00..2.27
rows=1 width=4) (actual time=0.013..0.050 rows=2 loops=1)
                     Filter: (((pse_normalized_text)::text = '1'::text)
AND (pse_interest_type = 10))
               ->  Index Scan using index_pse_09_on_part_1 on
tbl_profile_search_interest_1 tbl_profile_search  (cost=0.00..4.73
rows=1 width=4) (actual time=0.051..0.051 rows=0 loops=1)
                     Index Cond: ((pse_normalized_text)::text = '1'::text)
                     Filter: (pse_interest_type = 10)
               ->  Bitmap Heap Scan on tbl_profile_search_interest_10
tbl_profile_search  (cost=3581.12..16542.78 rows=333177 width=4) (actual
time=56.481..56.573 rows=48 loops=1)
                     Recheck Cond: ((pse_normalized_text)::text = '1'::text)
                     Filter: (pse_interest_type = 10)
                     ->  Bitmap Index Scan on index_pse_09_on_part_10
(cost=0.00..3581.12 rows=333177 width=0) (actual time=54.999..54.999
rows=341233 loops=1)
                           Index Cond: ((pse_normalized_text)::text =
'1'::text)
 Total runtime: 57.396 ms


-- 

   === The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
   Providing the most comprehensive  PostgreSQL solutions since 1997
             http://www.commandprompt.com/

Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate


Responses

pgsql-hackers by date

Next:From: markDate: 2006-10-24 16:47:09
Subject: Re: New CRC algorithm: Slicing by 8
Previous:From: Simon RiggsDate: 2006-10-24 16:05:58
Subject: Re: New CRC algorithm: Slicing by 8

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group