Tag Info

New answers tagged

1

VACUUM ANALYZE makes the difference in your example. Plus, as @jjanes supplied, the additional statistics for the functional index. Per documentation: pg_statistic also stores statistical data about the values of index expressions. These are described as if they were actual data columns; in particular, starelid references the index. No entry is made ...


4

When you create an expression index, it causes PostgreSQL to gather statistics on the that expression. With those statistics on hand, it now has an accurate estimate for the number of aggregated rows that the query will return, which leads it to make a better plan choice. Specifically in this case, without those extra statistics it thought the hash table ...


0

Just for fun, why not try this SQL instead, which rearranges things a bit: create table #t(name sysname) insert #t(Name) select Name from sys.databases where state_desc = 'ONLINE' except select name from ( values (cast('master' as sysname)), ('model'), ('msdb'), ('tempdb'), ('ReportServer'), ('ReportServerTempDB'), ...


0

Would This work? Got rid of case statement in where clause... SELECT name, OBJECT_ID(QUOTENAME(name) + '.[dbo].[_LOOK_UP_TABLE]', 'U') AS [LookUpID] FROM sys.databases WHERE name NOT IN ('master','model','msdb','tempdb','ReportServer','ReportServerTempDB','1303VISBOC') AND state_desc = 'ONLINE' AND ISNULL(OBJECT_ID(QUOTENAME(name) + ...


1

As @dezso suggested, the table with test data was not big enough for the query planner bother going to the index. After I imported a larger set of data, the query uses the index as expected.


0

The query is pretty simple and ought to run fast enough. My guess is that you have a long running transaction performing DDL in one of the databases and your query is getting blocked waiting on a lock. Unfortunately the metadata functions such as OBJECT_ID don't take account of the transaction isolation level of the outer transaction so even setting the ...



Top 50 recent answers are included