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.



Top 50 recent answers are included