I have a table, let's say 'tableone' with some thousands rows, on that
table I created an index on a single field, let's say 'fieldone' of type
character(3).
I have also a view, let's say 'viewone' that selects rows from tableone
for a certain value of fieldone (created with the query "select * from
tableone where fieldone = 'one'" ), so if I try:
select * from tableone where fieldone = 'one'
or
select * from viewone
obviously I get the same result and the same performance: a few ms,
Then I have a complex query that has two complete different
performances, in these two cases:
case a)
with getfieldone as (select * from tableone where fieldone = 'one')
select * from getfieldone
inner join tabletwo on ... [and other joins]
case b)
select * from viewone
inner join tabletwo on ... [and other joins, the same as case a)]
I expected the same performance, but case a) lasts a few hundreds ms,
while case b) lasts more than 12 seconds. In both cases I execute as
explain I see that selecting the table the proper index was involved,
The database is a test one, so I'm the sole user, and I act as the owner
role. I don't understand this behavior. What should I do?
Any advise?