By markus_petrux on

Can somone with PostgreSQL skills review this issue?

http://drupal.org/node/48415

Comments

grcm’s picture

grcm commented

I'm not yet running with the 4.7 / CVS Drupal so can't actually run the query myself on postgresql for analysis. I'll see what happens on 4.6 if you like :-)

Can you do the EXPLAIN in MySQL so we can see what the query does? There's a lot of performance-related discussion on the patch thread - but not a single EXPLAIN!

Thanks.

-- Version Control your Drupal web site with The File High Club's Free Trial!

grcm’s picture

grcm commented

I don't know how the 4.7/CVS accesslog table looks, but the CONCAT works in PostgreSQL.

Here are the results:

# \d accesslog
                                   Table "public.accesslog"
  Column   |          Type          |                        Modifiers                        
-----------+------------------------+---------------------------------------------------------
 aid       | integer                | not null default nextval('accesslog_aid_seq'::regclass)
 mask      | character varying(255) | not null default ''::character varying
 title     | character varying(255) | 
 path      | character varying(255) | 
 url       | character varying(255) | 
 hostname  | character varying(128) | 
 uid       | integer                | default 0
 timestamp | integer                | not null default 0
Indexes:
    "accesslog_pkey" PRIMARY KEY, btree (aid)
    "accesslog_timestamp_idx" btree ("timestamp")

=# explain SELECT COUNT(DISTINCT(CONCAT(uid, hostname))) FROM accesslog;
                             QUERY PLAN                             
--------------------------------------------------------------------
 Aggregate  (cost=78.65..78.67 rows=1 width=20)
   ->  Seq Scan on accesslog  (cost=0.00..72.52 rows=2452 width=20)

The performance hit is the COUNT - PostgreSQL can't use indexes for the count and has to scan the data. If you have a lot of rows in that table, it will take time.

How often is this actually called? is it an issue? Can it be cached?

-- Version Control your Drupal web site with The File High Club's Free Trial!

markus_petrux’s picture

markus_petrux commented

It is just used in admin/logs/visitors to help the inner tasks of the table pager.

The issue is that the current statement is SELECT COUNT(DISTINCT(uid)) FROM {accesslog} which returns and incorrect result so the pager generates more links (pages) than necessary.

The real query used to retrieve the table elements uses a couple of JOINs and GROUP BY a.hostname, a.uid, u.name, ac.aid. From that I found that the minimal set of fields that would return the correct result for the count query was to use CONCAT(uid, hostname) for the count.

It think it looks so tricky so there has been some debate in the issue, to maybe find a possible alternative. Steven also pointed out:

I've had issues with COUNT(DISTINCT()) and PgSQL before. Needs a PgSQL check before committing.

The current status of the issue is that admin/logs/visitors computes a wrong number of pages, so we need to find an alternative or, maybe, confirm the proposed change doesn't break anything for PostgreSQL users.

Thanks for checking :-)

Doubt is the beginning, not the end of wisdom.

grcm’s picture

grcm commented

It's not used in public-facing pages so performance is not so essential. Apart from performance, the COUNT(DISTINCT(CONCACT())) option appears to work fine in Postgres.

I think you need Steven to explain what his "issues" are.

(Normally I wouldn't condone the use of DISTINCT here, but it's not performance-sensitive.)

-- Version Control your Drupal web site with The File High Club's Free Trial!