Re: PostgreSQL Query Speed Issues

Joseph Pravato <joseph(dot)pravato(at)nomagic(dot)com> writes:
> Thanks again to everyone that has been assisting us with our query speed
> issue. We have possibly found a reason why our views are running slow,
> does anyone know if that could be our problem?
> Please read below.
>> We've started to test more of our queries that involve the view and we
>> believe we may have come across a possible reason for the poor
>> performance in our views.
>> The view is pulling data from many tables as said before, one of them
>> has a varchar(255) column (ColA) that is copied over into the view.
>> However, in the view column definition, it is a varchar(2147483647).
>> In the query we tested, we are running an equivalence check against this
>> column, along with another varchar column of size 1 (ColB). When we
>> remove the check against ColA the query returns in 2.5 seconds. When
>> included it talks 200 seconds. There is almost no difference when the
>> ColB check is removed (2.5s vs 2.3s).
>>
>> It is of our belief that this varchar(2147483647) could be causing
>> performance problems. ColA might be defaulting to 2147483647 because it
>> is being union-ed with the same column a couple of times in different
>> circumstances. So we are wondering if there a way to set the column's
>> varchar size in a view definition?

In principle that should not make any difference. (We have had bugs in
the query planner in the past that were triggered by such things ... but
not recently.)

You would be more likely to get useful answers if you posted the table
schemas and EXPLAIN ANALYZE results on pgsql-performance. There are
some tips on asking good questions here:
https://wiki.postgresql.org/wiki/Slow_Query_Questions

regards, tom lane