From: | Alessandro Manzoni <manzoni(dot)alessandro4(at)gmail(dot)com> |
---|---|
To: | pgsql-admin(at)postgresql(dot)org |
Subject: | Strange performance problem |
Date: | 2019-01-25 17:02:24 |
Message-ID: | 0f9efbe6-9adc-2732-1257-b4fc0367b0f3@gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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?
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2019-01-25 17:22:02 | Re: Strange performance problem |
Previous Message | Shreeyansh Dba | 2019-01-25 10:30:40 | Re: Creation of temporary tables on a publisher |