Created
April 9, 2020 06:09
-
-
Save dhkaneda/5b0b4c2ac215ff552683a81f6f32cd07 to your computer and use it in GitHub Desktop.
SDC Sample Core Query + Results (Postgres vs Cassandra)
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| /////////////// READ - Postgres //////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////// | |
| reservation_service=# explain analyze select * from bookings where bookings.locationid=240132; | |
| QUERY PLAN | |
| ------------------------------------------------------------------------------------------------------------------------------- | |
| Gather (cost=1000.00..433036.91 rows=12 width=102) (actual time=8643.416..10070.472 rows=8 loops=1) | |
| Workers Planned: 2 | |
| Workers Launched: 2 | |
| -> Parallel Seq Scan on bookings (cost=0.00..432035.71 rows=5 width=102) (actual time=7491.406..10063.379 rows=3 loops=3) | |
| Filter: (locationid = 240132) | |
| Rows Removed by Filter: 6666664 | |
| Planning Time: 0.152 ms | |
| Execution Time: 10070.504 ms | |
| (8 rows) | |
| Time: 10072.034 ms (00:10.072) | |
| reservation_service=# select * from bookings where bookings.locationid=240132; | |
| bookingid | userid | locationid | checkin | checkout | total_cost | adults | children | infants | |
| -----------+----------+------------+-----------------------------------------+-----------------------------------------+------------+--------+----------+--------- | |
| 18438420 | 291623 | 240132 | Wed Apr 29 2020 10:16:03 GMT-0700 (PDT) | Sat May 02 2020 10:16:03 GMT-0700 (PDT) | 759 | 5 | 12 | 1 | |
| 16375730 | 6836392 | 240132 | Thu May 14 2020 09:13:22 GMT-0700 (PDT) | Fri May 15 2020 09:13:22 GMT-0700 (PDT) | 271 | 3 | 1 | 1 | |
| 17012483 | 4685831 | 240132 | Tue May 26 2020 01:56:47 GMT-0700 (PDT) | Sat Jun 06 2020 01:56:47 GMT-0700 (PDT) | 2970 | 4 | 0 | 1 | |
| 8035165 | 16051001 | 240132 | Sun Jul 05 2020 11:29:11 GMT-0700 (PDT) | Fri Jul 10 2020 11:29:11 GMT-0700 (PDT) | 1190 | 7 | 11 | 0 | |
| 19106567 | 1861499 | 240132 | Mon May 25 2020 00:03:28 GMT-0700 (PDT) | Tue Jun 02 2020 00:03:28 GMT-0700 (PDT) | 1976 | 3 | 4 | 3 | |
| 18966909 | 1688994 | 240132 | Thu Jun 18 2020 17:05:00 GMT-0700 (PDT) | Fri Jun 19 2020 17:05:00 GMT-0700 (PDT) | 121 | 2 | 9 | 3 | |
| 18900011 | 1789720 | 240132 | Fri Jun 26 2020 02:35:59 GMT-0700 (PDT) | Sun Jul 05 2020 02:35:59 GMT-0700 (PDT) | 1152 | 2 | 10 | 1 | |
| 19288708 | 655764 | 240132 | Sun Jun 07 2020 20:59:09 GMT-0700 (PDT) | Sun Jun 14 2020 20:59:09 GMT-0700 (PDT) | 994 | 5 | 11 | 3 | |
| (8 rows) | |
| Time: 10964.514 ms (00:10.965) | |
| reservation_service=# | |
| /////////////// READ - Cassandra /////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////// | |
| cqlsh:bookings> select * from locations where locationid=240132; | |
| locationid | checkin | address | adults | avg_rating | bookingid | checkout | children | cleaning_fee | hostid | infants | occupancy_tax | rate | service_fee | total_cost | total_reviews | userid | |
| ------------+-----------------------------------------+------------------------+--------+------------+-----------+-----------------------------------------+----------+--------------+---------+---------+---------------+------+-------------+------------+---------------+---------- | |
| 240132 | Fri Apr 24 2020 05:36:42 GMT-0700 (PDT) | 806 Jocelyn Plaza | 5 | 3 | 12174256 | Tue May 05 2020 05:36:42 GMT-0700 (PDT) | 7 | 22 | 1126607 | 0 | 23.7 | 158 | 46 | 2431 | 1956 | 17149490 | |
| 240132 | Mon Jun 15 2020 23:44:04 GMT-0700 (PDT) | 970 Hassan Trail | 4 | 3 | 2861068 | Tue Jun 16 2020 23:44:04 GMT-0700 (PDT) | 12 | 28 | 733510 | 3 | 19.05 | 127 | 37 | 68 | 854 | 28253657 | |
| 240132 | Thu Apr 23 2020 21:05:32 GMT-0700 (PDT) | 6840 Justina Crossroad | 2 | 5 | 6242690 | Fri May 01 2020 21:05:32 GMT-0700 (PDT) | 3 | 28 | 1928835 | 3 | 9.3 | 62 | 33 | 2272 | 650 | 23612990 | |
| 240132 | Thu Jun 04 2020 19:39:45 GMT-0700 (PDT) | 993 Emmanuel Lights | 7 | 3 | 18533470 | Mon Jun 08 2020 19:39:45 GMT-0700 (PDT) | 10 | 14 | 184289 | 3 | 27 | 180 | 45 | 1056 | 1601 | 499733 | |
| 240132 | Thu May 14 2020 14:31:30 GMT-0700 (PDT) | 4180 Gerlach Forest | 1 | 3 | 9331292 | Tue May 19 2020 14:31:30 GMT-0700 (PDT) | 5 | 27 | 418430 | 1 | 9.45 | 63 | 39 | 1010 | 1795 | 1021852 | |
| 240132 | Tue Apr 14 2020 10:42:25 GMT-0700 (PDT) | 226 Heller Turnpike | 4 | 2 | 8733798 | Sat Apr 25 2020 10:42:25 GMT-0700 (PDT) | 9 | 11 | 1369988 | 0 | 26.4 | 176 | 35 | 990 | 577 | 4790314 | |
| 240132 | Tue Apr 21 2020 04:14:29 GMT-0700 (PDT) | 74514 Murazik Mountain | 1 | 2 | 12959976 | Sat May 02 2020 04:14:29 GMT-0700 (PDT) | 6 | 26 | 436543 | 1 | 44.55 | 297 | 46 | 3135 | 1610 | 11811830 | |
| 240132 | Tue May 12 2020 15:18:08 GMT-0700 (PDT) | 009 Doyle Turnpike | 5 | 2 | 1206265 | Tue May 26 2020 15:18:08 GMT-0700 (PDT) | 10 | 14 | 543495 | 0 | 29.55 | 197 | 34 | 2324 | 1307 | 32030544 | |
| 240132 | Tue May 26 2020 01:10:17 GMT-0700 (PDT) | 83315 Rogahn Station | 6 | 3 | 7635596 | Sun Jun 07 2020 01:10:17 GMT-0700 (PDT) | 4 | 19 | 471134 | 0 | 43.8 | 292 | 30 | 2568 | 1971 | 17545136 | |
| (9 rows) | |
| Tracing session: 6da64d70-7a27-11ea-afa6-9d1a959210eb | |
| activity | timestamp | source | source_elapsed | client | |
| -----------------------------------------------------------------------------------------+----------------------------+-----------+----------------+----------- | |
| Execute CQL3 query | 2020-04-08 23:00:35.015000 | 127.0.0.1 | 0 | 127.0.0.1 | |
| Parsing select * from locations where locationid=240132; [Native-Transport-Requests-1] | 2020-04-08 23:00:35.015000 | 127.0.0.1 | 151 | 127.0.0.1 | |
| Preparing statement [Native-Transport-Requests-1] | 2020-04-08 23:00:35.015000 | 127.0.0.1 | 285 | 127.0.0.1 | |
| Executing single-partition query on locations [ReadStage-3] | 2020-04-08 23:00:35.017000 | 127.0.0.1 | 1905 | 127.0.0.1 | |
| Acquiring sstable references [ReadStage-3] | 2020-04-08 23:00:35.017000 | 127.0.0.1 | 1963 | 127.0.0.1 | |
| Skipped 0/6 non-slice-intersecting sstables, included 0 due to tombstones [ReadStage-3] | 2020-04-08 23:00:35.017000 | 127.0.0.1 | 2067 | 127.0.0.1 | |
| Partition index with 0 entries found for sstable 176 [ReadStage-3] | 2020-04-08 23:00:35.023000 | 127.0.0.1 | 8007 | 127.0.0.1 | |
| Bloom filter allows skipping sstable 217 [ReadStage-3] | 2020-04-08 23:00:35.027000 | 127.0.0.1 | 12159 | 127.0.0.1 | |
| Partition index with 0 entries found for sstable 243 [ReadStage-3] | 2020-04-08 23:00:35.031000 | 127.0.0.1 | 15646 | 127.0.0.1 | |
| Partition index with 0 entries found for sstable 197 [ReadStage-3] | 2020-04-08 23:00:35.038000 | 127.0.0.1 | 23190 | 127.0.0.1 | |
| Bloom filter allows skipping sstable 257 [ReadStage-3] | 2020-04-08 23:00:35.043000 | 127.0.0.1 | 27831 | 127.0.0.1 | |
| Bloom filter allows skipping sstable 252 [ReadStage-3] | 2020-04-08 23:00:35.044000 | 127.0.0.1 | 28805 | 127.0.0.1 | |
| Merged data from memtables and 3 sstables [ReadStage-3] | 2020-04-08 23:00:35.044000 | 127.0.0.1 | 29323 | 127.0.0.1 | |
| Read 9 live rows and 0 tombstone cells [ReadStage-3] | 2020-04-08 23:00:35.046000 | 127.0.0.1 | 30845 | 127.0.0.1 | |
| Request complete | 2020-04-08 23:00:35.046529 | 127.0.0.1 | 31529 | 127.0.0.1 | |
| cqlsh:bookings> |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment