Re: [HACKERS] Incorrect behavior with CE and ORDER BY

2006-10-25 Thread Matteo Beccati
Tom Lane ha scritto: Alvaro Herrera [EMAIL PROTECTED] writes: Limit (50) Sort (key: pse_lastlogin) Result Append Limit (50) SeqScan tbl_profile_search Limit (50) Indexscan tbl_profile_search_interest_1 Limit (50)

[HACKERS] Incorrect behavior with CE and ORDER BY

2006-10-24 Thread Joshua D. Drake
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

Re: [HACKERS] Incorrect behavior with CE and ORDER BY

2006-10-24 Thread Tom Lane
Joshua D. Drake [EMAIL PROTECTED] writes: We have a problem with CE that I want to verify is either expected behavior, a bug or something else :). Uh, what's your problem exactly? The example only seems to demonstrate that if you don't ask for a sort, you don't get one.

Re: [HACKERS] Incorrect behavior with CE and ORDER BY

2006-10-24 Thread Joshua D. Drake
Tom Lane wrote: Joshua D. Drake [EMAIL PROTECTED] writes: We have a problem with CE that I want to verify is either expected behavior, a bug or something else :). Uh, what's your problem exactly? The example only seems to demonstrate that if you don't ask for a sort, you don't get one.

Re: [HACKERS] Incorrect behavior with CE and ORDER BY

2006-10-24 Thread Alvaro Herrera
Joshua D. Drake wrote: Tom Lane wrote: Joshua D. Drake [EMAIL PROTECTED] writes: We have a problem with CE that I want to verify is either expected behavior, a bug or something else :). Uh, what's your problem exactly? The example only seems to demonstrate that if you don't ask for

Re: [HACKERS] Incorrect behavior with CE and ORDER BY

2006-10-24 Thread Joshua D. Drake
Alvaro Herrera wrote: Joshua D. Drake wrote: Tom Lane wrote: Joshua D. Drake [EMAIL PROTECTED] writes: We have a problem with CE that I want to verify is either expected behavior, a bug or something else :). Uh, what's your problem exactly? The example only seems to demonstrate that if you

Re: [HACKERS] Incorrect behavior with CE and ORDER BY

2006-10-24 Thread Alvaro Herrera
I followed up with Joshua on Jabber. This is the query: SELECT pse_userid FROM tbl_profile_search WHERE pse_normalized_text='1' and pse_interest_type = 10 order by pse_lastlogin DESC limit 50 offset 0 I suggested adding an index on (pse_normalized_text, pse_lastlogin), on the assumption that

Re: [HACKERS] Incorrect behavior with CE and ORDER BY

2006-10-24 Thread Tom Lane
Alvaro Herrera [EMAIL PROTECTED] writes: Is this possible? It would be very fast. It's possible but not exactly simple. As an example, your proposed plan: Limit (50) Sort (key: pse_lastlogin) Result Append Limit (50) SeqScan tbl_profile_search