Question 114745 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – Why does the number of returned samples where name='keyword' does not match the number of observed samples with 'keyword' in table?

I have a Postgres table whose header is [id(uuid), name(str), arg_name(str), measurements(list), run_id(uuid), parent_id(uuid)] with a total of 237K entries.

When I want to filter for specific measurements I can use ‘name’, but for the majority of entries in the table ‘name’ == ‘arg_name’ and thus map to the same sample.

In my peculiar case I am interested in retrieving samples whose ‘name’=’TimeM12nS’ and whose ‘arg_name’=’Time’. These two attributes point to the same samples when visually inspecting the table through PgAdmin. That is to say all entries which have arg_name=’Time’ also have the name=’TimeM12nS’ and vice-versa.

Its obvious there’s a problem because of the quantity of returned samples is not the same. I first noticed the problem using django orm, but the problem is also present when I query the DB using PgAdmin.

SELECT *
FROM TableA
WHERE name='TimeM12nS'

returns 301 entries (name=’TimeM12nS’ and arg_name=’Time’ in all cases)

BUT the query:

SELECT *
FROM TableA
WHERE arg_name='Time'

returns 3945 (name=’TimeM12nS’ and arg_name=’Time’ in all cases)

I am completely stumped, anyone think they can shed some light into what’s happening here?

EDIT:
I should add that the query by ‘arg_name’ returns the 301 entries that are returned when querying by ‘name’

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    First let me say thank you to everyone who pitched in ideas to solve this conundrum and especially to JGH for the solution (found in the comments of the original post).

    Indeed the problem was a indexing issue. After re-indexing the queries return the same number of entries '3945' as expected.

    In Postgress re-indexing a table can be achieved through pgAdmin by navigating to Databases > 'database_name' > Schemas > Tables then right-clicking on the table_name selecting Maintenance and pressing the REINDEX button.

    or more simply by running the following command

    REINDEX TABLE table_name

    Postgress Re-Indexing Docs


  2. 0

    Without access to the database, it’s not possibly to give a definitive answer. All I can provide is the next query that I would use in this case.

    SELECT COUNT(*), LENGTH(name), name, arg_name
FROM TableA
WHERE arg_name='Time'
GROUP BY name, arg_name;

    This should show you any differences in the name column that you aren’t able to see. The length of that string could also be informative.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search