pg.ddx.io pgsql-admin@postgresql.org mailing list archivehelp / color / mirror / Atom feed
Strange performance problem 2+ messages / 2 participants [nested] [flat]
* Strange performance problem @ 2019-01-25 17:02 Alessandro Manzoni <manzoni.alessandro4@gmail.com> 2019-01-25 17:22 ` Re: Strange performance problem Tom Lane <tgl@sss.pgh.pa.us> 0 siblings, 1 reply; 2+ messages in thread From: Alessandro Manzoni @ 2019-01-25 17:02 UTC (permalink / raw) To: pgsql-admin 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? ^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: Strange performance problem 2019-01-25 17:02 Strange performance problem Alessandro Manzoni <manzoni.alessandro4@gmail.com> @ 2019-01-25 17:22 ` Tom Lane <tgl@sss.pgh.pa.us> 0 siblings, 0 replies; 2+ messages in thread From: Tom Lane @ 2019-01-25 17:22 UTC (permalink / raw) To: Alessandro Manzoni <manzoni.alessandro4@gmail.com>; +Cc: pgsql-admin Alessandro Manzoni <manzoni.alessandro4@gmail.com> 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 ^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2019-01-25 17:22 UTC | newest] Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2019-01-25 17:02 Strange performance problem Alessandro Manzoni <manzoni.alessandro4@gmail.com> 2019-01-25 17:22 ` Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions for how to clone and mirror all data and code used for this inbox