Why different speed between oracle and postgresql?

Question

In postgresql

select * from test where text123 = '1'

"Index Scan using ix_test on test (cost=0.57..12619.44 rows=6980 width=343)"
"  Index Cond: ((text123)::text = '1'::text)"



select * from test where text123 = ''

"Seq Scan on test (cost=0.00..11918891.20 rows=209355618 width=343)"
"  Filter: ((text123)::text = ''::text)"

first query result returns immediately. but second isn't.

In Oracle, it has same plan with postgresql but second query result returns immediately.

What can I do for second query in postgresql? And why second query is so slow?

please help me...

Try analyze test; in postgresql. It should just recompute the stats on the test table so the query plan can be the best available. Change the select list when you test the query again, eg select text123 from test where text123 = '1' so you can be sure it's re-preparing the query plan. Report back with the result - this may or may not fix it immediately. — Peter M.
– Peter M., Commented Jul 12, 2016 at 1:57
First, thanks about your advise. I tried your suggestion. but, it is not working. Can you give another way for me? — 김동우
– 김동우, Commented Jul 12, 2016 at 2:33
In Oracle '' is the same as NULL. In PostgreSQL they are not the same. No idea why comparing to '' (empty string) should take longer than comparing to '1' - perhaps there are very few rows with '1' but many with ''? Try where text123 IS NULL in PostgreSQL and see what happens. — user5683823
– user5683823, Commented Jul 12, 2016 at 2:34
It isn't full query. It is subquery for another main query. This subquery return result ''. so I tested like that. — 김동우
– 김동우, Commented Jul 12, 2016 at 2:36

Bob Jarvis - Слава Україні · Accepted Answer · 2016-07-12 02:35:44Z

2

The second query

select * from test where text123 = ''

does very different things in PostgreSQL and Oracle.

In PostgreSQL '' means "a string of zero length". Thus in PostgreSQL the query means

Return all fields in all rows of table test where the text123 column
is equal to a string which is zero characters long

In Oracle, however '' means NULL. Thus in Oracle this query means

Return all fields in all rows of table TEST where the TEXT123 column
is equal to NULL

and because nothing, not even NULL, is ever equal to NULL the query will return nothing.

Best of luck.

answered Jul 12, 2016 at 2:35

Bob Jarvis - Слава Україні

50.2k10 gold badges81 silver badges119 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

김동우 Over a year ago

Thank you! I got useful knowledge from you.

Collectives™ on Stack Overflow

Why different speed between oracle and postgresql?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related