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 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) |
> (...)
> 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 | www.neadwerx.com

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