I discovered that Joomla generates lots of queries requesting article tags. Often tag queries are unnecessary when tags are not used at all on a certain page, but these queries are still retrieved.

How to test

Let's show e.g. 15 articles on a category blog page: New Menu Item > Articles > Category Blog
Leading Articles: 1
Intro Articles: 4
Links: 10

In Joomla! Debug Console > Database Queries we have lots of similar queries like this:

SELECT `m`.`tag_id`,`t`.*

  FROM `sj3d2_contentitem_tag_map` AS m 

  INNER JOIN `sj3d2_tags` AS t  
  ON `m`.`tag_id` = `t`.`id`

  WHERE `m`.`type_alias` = 'com_content.article' 
  AND `m`.`content_item_id` = 36 
  AND `t`.`published` = 1 
  AND t.access IN (1,1)

This query retrieves tag information for an article, so there will be 15 such queries (one for each article we show).

We decided not to show tags on this category page. We go to menu item settings:
Options > Show Tags : Hide

There is no more tags on this category page. Now let's have a look at Database Queries in Joomla! Debug Console again. We see nothing has changes, and we still get 15 queries for tag information that is not going to be used on this page.

Let's go further and show Articles Category Module on this category page. In this module we show 15 articles from the same category. Something like this:

Let's check how Database Queries have changed.
We see +15 similar tag queries. If it happens that articles in blog category and in the module are the same, the queries will be duplicated, which Joomla! Debug Console will inform you about.

Summary

We have a simple category page with one module. There are no tags showed on this page, but we still have 30 unnecessary tag queries:

• 15 queries belong to Category Blog with settings Show Tags: Hide;
• 15 queries belong to Articles Category Module which never shows any tags.

I find such results awful and bad for site performance. Tags are not always used, often they are used just for several categories on a site, while the others don't use tags at all.
Why not making such categories display quicker?

Possible fix

Debug Console > Query Call Stack helped me to localize where it happens:
JHelperTags->getItemTags() JROOT\components\com_content\models\articles.php:663

Open articles.php file and find lines:

            // Get the tags
            $item->tags = new JHelperTags;
            $item->tags->getItemTags('com_content.article', $item->id);

Add condition depending on article Show Tags settings so that we get tag information only when it's necessary:

            // Get the tags
            if ($item->params->get('show_tags')) {
                $item->tags = new JHelperTags;
                $item->tags->getItemTags('com_content.article', $item->id);
            }

Before:

54 Queries (includes 30 duplicates)
Database queries total: 66 ms

After:

24 Queries (0 duplicates)
Database queries total: 41 ms

The results speak for themselves.
I'm not sure how valid my fix is. But I'm sure that this issue must be solved, and cannot be left as it is now. Because creating tag queries for every article in cases when it's unnecessary seems to me a big mistake which can have a negative impact on site performance.