where clause gets munged

[shribe]

I don't know if it's in the capture or the analysis, but it's displaying the following to me:

SELECT COUNT(*) FROM "Appointment" WHERE ("Person_Id" = '2015-05-28 00:00:00'::timestamp without time zone and "CanceledWhen" is null and "AptWhen" >= 21524038::integer)

I only find one place in my code with those criteria, and it does not have the person id & time criteria reversed. In fact, the query as reported would not even execute because of the type mismatches.

[rdunklau]

Could you provide us with a schema for your table, including index definition ? What is the execution plan of this query ?

If the query is still in pg_qualstats cache, could you attach the content of the the pg_qualstats() function call ? eg: SELECT * FROM pg_qualstats().

Thank you.

[shribe]

Here's the info you asked for. FYI this is with PG 9.4.1, and PoWA installed last night from download of zip from this site. Also, as I look at more queries, it's not just this one. I've only looked at a few, but they've all been wrong.

\d "Appointment" Table "v2.Appointment" Column | Type | Modifiers ---------------------+--------------------------------+---------------------------------------------------------- id | bigint | not null default nextval(('"DbRowIds"'::text)::regclass) Person_Id | bigint | ReassignmentFrom_Id | bigint | ReassignmentTo_Id | bigint | CreatedWhen | timestamp(0) with time zone | default ('now'::text)::timestamp(0) with time zone LastChangedWhen | timestamp(0) with time zone | Restricted | boolean | not null default false Staff_Id | bigint | not null AptWhen | timestamp(0) without time zone | not null Duration | interval(0) | not null AptType_Id | bigint | Description | character varying | Notes | character varying | BookedByStaff_Id | bigint | CanceledWhen | timestamp(0) with time zone | CanceledByStaff_Id | bigint | CancelReason | character varying | PreRegistered | boolean | not null default false Indexes: "Appointment_pkey" PRIMARY KEY, btree (id) "AppointmentBookedBy" btree ("BookedByStaff_Id") "Appointment__CanceledBy" btree ("CanceledByStaff_Id") "AppointmentPt_When" btree ("Person_Id", "AptWhen") "AppointmentStaff" btree ("Staff_Id") "Appointment__Type" btree ("AptType_Id") "AppointmentWhen_Staff" btree ("AptWhen", "Staff_Id") CLUSTER

explain SELECT COUNT(*) FROM "Appointment" WHERE ("Person_Id" = 21524038::integer and "CanceledWhen" is null and "AptWhen" >= '2015-05-28 00:00:00'::timestamp without time zone);

QUERY PLAN

Aggregate (cost=4.45..4.46 rows=1 width=0) -> Index Scan using "Appointment__Pt_When" on "Appointment" (cost=0.42..4.44 rows=1 width=0) Index Cond: (("Person_Id" = 21524038) AND ("AptWhen" >= '2015-05-28 00:00:00'::timestamp without time zone)) Filter: ("CanceledWhen" IS NULL) (4 rows)

select * from pg_stat_statements where queryid = 1764367116; userid | dbid | queryid | query | calls | total_time | rows | shared_blks_hit | shared_blks_read | shared_blks_dirtied | shared_blks_written | local_blks_hit | local_blks_read | local_blks_dirtied | local_blks_written | temp_blks_read | temp_blks_written | blk_read_time | blk_write_time --------+-------+------------+----------------------------------------------------------------------------------------------------------+-------+------------+------+-----------------+------------------+---------------------+---------------------+----------------+-----------------+--------------------+--------------------+----------------+-------------------+---------------+---------------- 10 | 16388 | 1764367116 | SELECT COUNT(*) FROM "Appointment" WHERE ("Person_Id" = ? and "CanceledWhen" is null and "AptWhen" >= ?) | 9 | 275.124 | 9 | 25 | 5 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 207.577 | 0 (1 row)

select * from pg_qualstats() where queryid = 1764367116; userid | dbid | lrelid | lattnum | opno | rrelid | rattnum | qualid | uniquequalid | qualnodeid | uniquequalnodeid | count | nbfiltered | constant_position | queryid | constvalue | eval_type --------+-------+--------+---------+------+--------+---------+-----------+--------------+------------+------------------+-------+------------+-------------------+------------+----------------------------------------------------+----------- 10 | 16388 | 18824 | 9 | 2065 | | | 258418561 | 1494580591 | 1336931104 | 2101374075 | 0 | 0 | 118 | 1764367116 | '2015-05-28 00:00:00'::timestamp without time zone | i 10 | 16388 | 18824 | 2 | 416 | | | 258418561 | 1494580591 | 264674946 | 397077709 | 0 | 0 | 56 | 1764367116 | 21524038::integer | i (2 rows)

[shribe]

FYI this is on OS X 10.10.3. Shouldn't matter, but of course there's always the possibility of platform-specific bugs in some library.

[shribe]

I left out the constraints, and the first 6 columns are inherited...

[rdunklau]

The following commit in pg_qualstats should fix your issue:

https://github.com/dalibo/pg_qualstats/commit/76321ccaf078d3a82d47c12914d758ee53ac84b1

However, already captured / aggregated values by powa will stay out of order.

[shribe]

Thanks. Not a problem with already captured data. I'm practicing on a db with excellent performance (worst query today so far is averaging 15ms) before I go to work on the one that needs help ;-)

[rjuju]

This issue seems to be fixed. Feel free to contact us again if it's not.

Regards.