strange query results

Anand Raman <araman@india-today.com> writes:

The second query is not priniting out site_section column.

Odd. What is the exact definition of table exhibit_distributions?
Does it have any indices? What plan is printed by EXPLAIN for the
problem query?

regards, tom lane

Hi tom
The table description is as follows

arttoday=> \d exhibit_distributions 
                                    Table "exhibit_distributions"
          Attribute           |     Type      |                       Modifier                       
------------------------------+---------------+------------------------------------------------------
 exhibit_distribution_id      | integer       | not null default nextval('sq_exhibit_dist_id'::text)
 exhibit_id                   | integer       | not null
 created_by                   | integer       | not null
 creation_date                | timestamp     | not null default "timestamp"('now'::text)
 last_update_date             | timestamp     | 
 last_updated_by              | integer       | 
 exhibit_type_id              | integer       | not null
 medium                       | varchar(100)  | 
 image_path_small             | varchar(50)   | 
 image_path_big               | varchar(50)   | 
 length                       | numeric(7,2)  | 
 breadth                      | numeric(7,2)  | 
 width                        | numeric(7,2)  | 
 diameter                     | numeric(7,2)  | default 8
 dimensional_aspect           | varchar(50)   | default 'Dimensions'
 unframed_volume_weight       | numeric(10,2) | 
 framed_volume_weight         | numeric(10,2) | 
 override_vw_computation      | boolean       | default 'f'::bool
 exhibit_code                 | varchar(25)   | 
 exhibit_options              | varchar(10)   | 
 unframed_exhibit_restriction | varchar(25)   | default 'WORLD'
 framed_exhibit_restriction   | varchar(25)   | default 'WORLD'
 up_for_sale                  | char(1)       | not null default 'T'
 gallery_id                   | integer       | 
 site_section                 | varchar(20)   | not null default 'GALLERY'
Index: exhibit_distributions_pkey
Constraints: ((up_for_sale = 'T'::bpchar) OR (up_for_sale = 'F'::bpchar))
             (length > '0'::"numeric")
             (breadth > '0'::"numeric")
             (width > '0'::"numeric")
             (((unframed_exhibit_restriction = 'WORLD'::"varchar") OR (unframed_exhibit_restriction = 'INDIA'::"varchar")) OR (unframed_exhibit_restriction = 'ONLY_WORLD'::"varchar"))
             (((framed_exhibit_restriction = 'WORLD'::"varchar") OR (framed_exhibit_restriction = 'INDIA'::"varchar")) OR (framed_exhibit_restriction = 'ONLY_WORLD'::"varchar"))
             (((exhibit_options = 'FRAMED'::"varchar") OR (exhibit_options = 'UNFRAMED'::"varchar")) OR (exhibit_options = NULL::"varchar"))

Explain plan results in the following
arttoday=> explain select site_section, count(*) from exhibit_distributions group by site_section;
NOTICE: QUERY PLAN:

Aggregate (cost=69.83..74.83 rows=100 width=12)
-> Group (cost=69.83..72.33 rows=1000 width=12)
-> Sort (cost=69.83..69.83 rows=1000 width=12)
-> Seq Scan on exhibit_distributions
(cost=0.00..20.00 rows=1000 width=12)

The problem still persists.. I will a drop and recreation of the db just
in case..

Thanks for the help
Anand

Anand Raman <araman@india-today.com> writes:

The table description is as follows

Hmm ... nothing obviously funny here. Is there anything unusual about
the history of this table? (For example, were site_section or any other
columns added via ALTER TABLE, rather than being there all along?)

regards, tom lane

Hi tom

Thanks for your time..

No all of the columns were there right from the start..
Even if i added a few columns i always went thru the process of dropping
and recreating the entire db..

However a few days back there was a instance of index curroption and things came
to a halt.. A vaccum of the database notified of the possible curroption
and i recreated a index which didnt belong to this table..

I havent tried vaccuming the table now.. Will it help??

Thanks
Anand

HI tom
A few days back i had bugged this list about the seemingly impossible
select queries results..

##RECAP##
select distinct site_section as "distinct site sections" from
exhibit_distributions ;
distinct site sections
------------------------
ARCHIVED
ARTETC
CALENDAR
GALLERY
POSTCARD
(5 rows)

select site_section, count(*) from exhibit_distributions group by
site_section;
site_section | count
--------------+-------
| 352
| 45
| 1
| 166
| 2

##RECAP##

On going thru the flat files which we used to uplaod the database we
noticed a few fields had '' characters to signify '.. This was creating
problems in some jdbc queries.

One changing them to single ' and reloading all the data, the problem
simply disappered..

Thanks for the help
Anand Raman