From: Alessandro Manzoni <manzoni.alessandro4@gmail.com>
To: pgsql-admin@postgresql.org
Subject: Strange performance problem
Date: Fri, 25 Jan 2019 18:02:24 +0100
Message-ID: <0f9efbe6-9adc-2732-1257-b4fc0367b0f3@gmail.com> (raw)
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?
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: manzoni.alessandro4@gmail.com
Subject: Re: Strange performance problem
In-Reply-To: <0f9efbe6-9adc-2732-1257-b4fc0367b0f3@gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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