CODEWITHSUNDEEP
oracle-databasepostgresql
김동우
김동우

Reputation: 19

Why different speed between oracle and postgresql?

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...

Upvotes: 0

Views: 54

Answers (1)

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

Reputation: 50017

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.

Upvotes: 2

Related Questions