Strange performance problem
Strange performance problem
От:
Alessandro Manzoni <manzoni.alessandro4@gmail.com>
Дата:
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?
Re: Strange performance problem
От:
Tom Lane <tgl@sss.pgh.pa.us>
Дата:
Alessandro Manzoni writes: > 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. WITH is an optimization fence (at present --- there is discussion of relaxing that) so it's not exactly surprising that these queries perform differently. I am a little surprised though that case a) is the faster one; usually, preventing the planner from optimizing the whole query as one problem makes things worse. Saying more than that would require a lot more detail. https://wiki.postgresql.org/wiki/Slow_Query_Questions regards, tom lane