Re: Can you spot the difference?

Statistics on your "original" and "copy" databases must be different.
For the same condition (li.tracking_number = '10137378459') optimizer expects to find 7915 rows in tb_line_item table on the "copy" database while only 54 rows on the "original" database.
Also, the other table (tb_order_location) could have bad statistics as well.
That could cause different execution plans.

Regards,
Igor Neyman

From: Moshe Jacobson [mailto:moshe(at)neadwerx(dot)com]
Sent: Tuesday, April 16, 2013 3:07 PM
To: pgsql-general
Subject: Can you spot the difference?

Hi PostgreSQL friends,

I have two databases in the same cluster that are almost identical. One is a copy of the other as we are developing some new features in the copy.

My problem is that the exact same simple query performs great in the original database ("ises") and dismally in the copy database ("ises_coelacanth"). The problem is that in ises, it uses an index scan, but in ises_coelacanth it uses a sequential scan:

postgres(at)moshe=>devmain:ises=# explain analyze SELECT count(*) FROM tb_order_location ol JOIN tb_line_item li on li.order_location = ol.order_location WHERE li.tracking_number = '10137378459';
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=671.48..671.49<tel:671.48..671.49> rows=1 width=0) (actual time=0.272..0.272 rows=1 loops=1)
-> Nested Loop (cost=0.00..671.34 rows=54 width=0) (actual time=0.124..0.265 rows=16 loops=1)
-> Index Scan using tb_line_item_tracking_number_key on tb_line_item li (cost=0.00..219.17 rows=54 width=4) (actual time=0.087..0.161 rows=16 loops=1)
Index Cond: ((tracking_number)::text = '10137378459'::text)
-> Index Scan using tb_order_location_pkey on tb_order_location ol (cost=0.00..8.36 rows=1 width=4) (actual time=0.005..0.005 rows=1 loops=16)
Index Cond: (order_location = li.order_location)
Total runtime: 0.343 ms
(7 rows)

postgres(at)moshe=>devmain:ises_coelacanth=# explain analyze SELECT count(*) FROM tb_order_location ol JOIN tb_line_item li on li.order_location = ol.order_location WHERE li.tracking_number = '10137378459';
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=50467.40..50467.41 rows=1 width=0) (actual time=333.490..333.491 rows=1 loops=1)
-> Hash Join (cost=26551.11..50447.62 rows=7915 width=0) (actual time=332.045..333.481 rows=16 loops=1)
Hash Cond: (li.order_location = ol.order_location)
-> Bitmap Heap Scan on tb_line_item li (cost=177.82..20715.03 rows=7915 width=4) (actual time=0.128..0.209 rows=16 loops=1)
Recheck Cond: ((tracking_number)::text = '10137378459'::text)
-> Bitmap Index Scan on tb_line_item_tracking_number_key (cost=0.00..175.84 rows=7915 width=0) (actual time=0.108..0.108 rows=16 loops=1)
Index Cond: ((tracking_number)::text = '10137378459'::text)
-> Hash (cost=13190.24..13190.24 rows=803524 width=4) (actual time=324.114..324.114 rows=803553 loops=1)
Buckets: 4096 Batches: 32 Memory Usage: 887kB
-> Seq Scan on tb_order_location ol (cost=0.00..13190.24 rows=803524 width=4) (actual time=0.024..144.581 rows=803553 loops=1)
Total runtime: 333.766 ms
(11 rows)

Both of these queries return 16 rows, as you can see.

Below I've included the information on each of these tables. They have the same indexes and are identical for the purposes of this query.

Can you help me figure out what is going on here?? Thank you!

postgres(at)moshe=>devmain:ises=# \d tb_line_item
Table "public.tb_line_item"
Column | Type | Modifiers
----------------------------+-----------------------------+-------------------------------------------------------
line_item | integer | not null default nextval('sq_pk_line_item'::regclass)
(...)
order_location | integer | not null
(...)
tracking_number | character varying(512) |
(...)
Indexes:
"tb_line_item_pkey" PRIMARY KEY, btree (line_item)
"tb_line_item_order_catalog_article_key" UNIQUE CONSTRAINT, btree (order_catalog_article, order_location, project, creator)
"tb_line_item_order_vendor_article_key" UNIQUE CONSTRAINT, btree (order_vendor_article, order_location, project, creator)
"idx_line_item_canceled" btree (canceled)
"ix_line_item_project" btree (project)
"ix_line_item_reset" btree (reset)
"tb_line_item_order_location_key" btree (order_location)
"tb_line_item_tracking_number_key" btree (tracking_number)
Check constraints:
"chk_order_vendor_article_or_order_catalog_article" CHECK (order_vendor_article IS NULL AND order_catalog_article IS NOT NULL OR order_vendor_article IS NOT NULL AND order_catalog_article IS NULL)
"tb_line_item_check" CHECK (
CASE
WHEN executed IS NOT NULL AND canceled IS NOT NULL THEN false
ELSE true
END)
"tb_line_item_quantity_backordered_check" CHECK (quantity_backordered >= 0::numeric)
"tb_line_item_quantity_ordered_check" CHECK (quantity_ordered <> 0::numeric)
"tb_line_item_unit_price_check" CHECK (unit_price >= 0::numeric)
Foreign-key constraints:
(...)
"tb_line_item_order_location_fkey" FOREIGN KEY (order_location) REFERENCES tb_order_location(order_location)
(...)
Referenced by:
TABLE "tb_shipment" CONSTRAINT "tb_shipment_line_item_fkey" FOREIGN KEY (line_item) REFERENCES tb_line_item(line_item)
Triggers:
(...)
postgres(at)moshe=>devmain:ises=# \d tb_order_location
Table "public.tb_order_location"
Column | Type | Modifiers
-----------------------------+-----------------------------+------------------------------------------------------------
order_location | integer | not null default nextval('sq_pk_order_location'::regclass)
orderid | integer | not null
(...)
Indexes:
"tb_order_location_pkey" PRIMARY KEY, btree (order_location)
"tb_order_location_orderid_key" UNIQUE CONSTRAINT, btree (orderid, location)
"tb_order_location_location_key" btree (location)
Foreign-key constraints:
"tb_order_location_location_fkey" FOREIGN KEY (location) REFERENCES tb_location(location)
"tb_order_location_orderid_fkey" FOREIGN KEY (orderid) REFERENCES tb_order(orderid)
Referenced by:
TABLE "tb_line_item" CONSTRAINT "tb_line_item_order_location_fkey" FOREIGN KEY (order_location) REFERENCES tb_order_location(order_location)
Triggers:
(...)

Here is the \d for tb_order_location and tb_line_item on ises_coelacanth:

postgres(at)moshe=>devmain:ises_coelacanth=# \d tb_line_item
Table "public.tb_line_item"
Column | Type | Modifiers
----------------------------+-----------------------------+-------------------------------------------------------
line_item | integer | not null default nextval('sq_pk_line_item'::regclass)
(...)
order_location | integer | not null
(...)
tracking_number | character varying(512) |
(...)
[https://mail.google.com/mail/u/1/images/cleardot.gif]
Indexes:
"tb_line_item_pkey" PRIMARY KEY, btree (line_item)
"tb_line_item_order_catalog_article_key" UNIQUE CONSTRAINT, btree (order_article_location, order_location, project, creator)
"idx_line_item_canceled" btree (canceled)
"idx_line_item_executed" btree (executed)
"ix_line_item_project" btree (project)
"ix_line_item_reset" btree (reset)
"tb_line_item_order_location_key" btree (order_location)
"tb_line_item_tracking_number_key" btree (tracking_number)
Check constraints:
"tb_line_item_check" CHECK (
CASE
WHEN executed IS NOT NULL AND canceled IS NOT NULL THEN false
ELSE true
END)
"tb_line_item_quantity_backordered_check" CHECK (quantity_backordered >= 0::numeric)
"tb_line_item_quantity_ordered_check" CHECK (quantity_ordered <> 0::numeric)
"tb_line_item_unit_price_check" CHECK (unit_price >= 0::numeric)
Foreign-key constraints:
(...)
"tb_line_item_order_location_fkey" FOREIGN KEY (order_location) REFERENCES tb_order_location(order_location)
(...)
Referenced by:
TABLE "tb_shipment" CONSTRAINT "tb_shipment_line_item_fkey" FOREIGN KEY (line_item) REFERENCES tb_line_item(line_item)
Triggers:
(...)
postgres(at)moshe=>devmain:ises_coelacanth=# \d tb_order_location
Table "public.tb_order_location"
Column | Type | Modifiers
-----------------------------+-----------------------------+------------------------------------------------------------
order_location | integer | not null default nextval('sq_pk_order_location'::regclass)
orderid | integer | not null
(...)
Indexes:
"tb_order_location_pkey" PRIMARY KEY, btree (order_location)
"tb_order_location_orderid_key" UNIQUE CONSTRAINT, btree (orderid, location)
"tb_order_location_location_key" btree (location)
Foreign-key constraints:
"tb_order_location_location_fkey" FOREIGN KEY (location) REFERENCES tb_location(location)
"tb_order_location_orderid_fkey" FOREIGN KEY (orderid) REFERENCES tb_order(orderid)
Referenced by:
TABLE "tb_line_item" CONSTRAINT "tb_line_item_order_location_fkey" FOREIGN KEY (order_location) REFERENCES tb_order_location(order_location)
Triggers:
(...)

--
Moshe Jacobson
Nead Werx, Inc. | Manager of Systems Engineering
2323 Cumberland Parkway, Suite 201 | Atlanta, GA 30339
moshe(at)neadwerx(dot)com<mailto:moshe(at)neadwerx(dot)com> | www.neadwerx.com<http://www.neadwerx.com/>

"Quality is not an act, it is a habit." -- Aristotle